Problems with like special characters in sql statements

wenzi123

name
_twenty three_
_3_


select name from table where name like '% _3_%'

This statement will select all the above 2 records. Don't believe you guys try it, didn't notice it before.
The first one is not what I want, is it _ special treatment?

gaoqingfeng

I have n’t really tried it yet. Then put it in []

我是杀人狂

select name from table where name like '%\_3\_%' escape '\'

小老鹰

where name like '% [_] 3 [_]%'

恶魔右指

declare @ta table (name varchar (10))
insert @ta
select '_23_'
union select '_3_'

--select name from @ta where name like '% _3_%' this way will not work without escaping
With ESCAPE:

select name from @ta where name like '%! _ 3! _%' ESCAPE '!'

(The number of rows affected is 2)

name
----------
_3_

(The number of rows affected is 1 row)

恶魔右指

Use ESCAPE_ to escape common symbols

fanfan1980

select * from
(
select col = '_ 23_'
union all select '_3_'
) a where col like '% a_3_%'
escape 'a'

--result
col
----
_3_

(1 row (s) affected)

dazhuzhu1h

ESCAPE is a good thing