How to handle special characters in SQL Server Select query

Question

I have following data in table

create table Manager(id int, managerid varchar(10) , managername varchar(50))

insert into Manager(id, managerid, managername)
values (1, 'A1', 'Mangesh'), (2, 'A''2', 'Sagar'), (3, '_C[%]3', 'Ah_mad'),
       (4, 'A4', 'Mango'), (5, 'B5', 'Sandesh')

I am using stored procedure to get the result on the basis of given characters. One of the users want to search with c[%]. I am able to handle % with [%] but if string is having [%] then I am unable to find it.

I tried with following query

declare @str varchar(100)='C[[%]'

SELECT m.* 
FROM Manager m 
WHERE m.managerid LIKE '%'+@str+'%'

This way I am able to get the result but in this case input is C[% only. I replaced % with [%] in my stored procedure parameter. But if input is C[%] then my query is not returning anything.

Question is not clear. Are you looking for managerid's that include c[%]? Have you tried charindex? — uzi, Jan 12 '18 at 15:10
_"I am unable to do this"_ is not a problem description. What did you try? What happened? Why was that wrong? — underscore_d, Jan 12 '18 at 15:11
Please include the code you used to search for `c[%]`. I suspect that you need to look for the `LIKE... ESCAPE` functionality though — Shaneis, Jan 12 '18 at 15:11
possible duplicate - https://stackoverflow.com/questions/18693349/how-do-i-find-with-the-like-operator-in-sql-server — Ctznkane525, Jan 12 '18 at 15:13

Valerica · Answer 1 · 2018-01-12T15:22:21.743

1

You have to escape the square bracket and % character using [], this tells SQL to treat them as literals.

So in order to find the % it needs escaped as [%], the same for the square bracket it needs escaped as [[]

You don't need to escape the ] because if it's not paired with [ has no special meaning.

select * from Manager where managerid like '%c[[][%]]%'

edited Jan 12 '18 at 15:22

answered Jan 12 '18 at 15:15

Valerica

1,618
1
13
20

score 0 · Answer 2 · answered Jan 12 '18 at 15:12

0

Use Escape to ignore the wildcards. Try this

select * from Manager 
where managerid like '%c\[%]%' escape '\'

answered Jan 12 '18 at 15:12

Pரதீப்

91,748
19
131
172

score 0 · Answer 3 · answered Jan 12 '18 at 15:19

0

You need to escape only one opening '['

SELECT * FROM MANAGER where managerid like '%_C[[%]%'

answered Jan 12 '18 at 15:19

shockwave

3,074
9
35
60

Oceans · Answer 4 · 2018-01-12T15:28:20.253

Both %, _ and [ are special characters and should be escaped. So it could be that even your INSERT-statement is wrong.

You can place the special characters between square brackets [ and ] to make them literal.

insert into Manager(id,managerid,managername)values(3,'[_]C[[][%]]3','Ah[_]mad')

You can use this same method to search on:

SELECT * FROM Manager
WHERE managerid LIKE '%C[[][%]]%'

Or define your own escape character like this:

SELECT * FROM Manager
WHERE managerid LIKE '%C\[\%]%' ESCAPE '\'

Here is some documentation that might be usefull to you: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql

score 0 · Answer 5 · answered Jan 12 '18 at 15:23

0

In case you are trying to searching with input 'C[%]'. Check this answer,

declare @str varchar(100)='C[%]'

SELECT m.* 
FROM Manager m 
WHERE REPLACE(m.managerid,'[','')    LIKE  '%'+@str+'%'

answered Jan 12 '18 at 15:23

Bibin Mathew

455
3
11

score 0 · Accepted Answer · answered Jan 12 '18 at 15:42

0

I got the solution.

declare @str varchar(100)='C\[\%]'

SELECT m.* 
FROM Manager m 
WHERE m.managerid LIKE '%'+@str+'%' ESCAPE '\'

It worked.

answered Jan 12 '18 at 15:42

Sharad

743
2
14
23

How to handle special characters in SQL Server Select query

6 Answers6