Sql where statement with = works but LIKE does not

Question

Sql where statement with = works but LIKE does not Is there a fix for this?

Here is the exact code:

create table zSyn (xField nvarchar(255));
insert into zSyn(xField)
select 'DEVCON 5 Minute Epoxy amber [1:1]';

--Works and returns 1 row:
select * from zSyn
where xField = 'DEVCON 5 Minute Epoxy amber [1:1]';

--Does NOT return any rows:
select * from zSyn
where xField like '%' + 'DEVCON 5 Minute Epoxy amber [1:1]' + '%'

score 6 · Accepted Answer · answered Jul 07 '19 at 15:33

6

You need to escape []:

select * from zSyn
where xField like ('%' + 'DEVCON 5 Minute Epoxy amber ![1:1!]' + '%') ESCAPE '!';

db<>fiddle demo

answered Jul 07 '19 at 15:33

Lukasz Szozda

162,964
23
234
275

1

Great eye! I would have looked at that a hundred times, and I would never see that +1 – John Cappelletti Jul 07 '19 at 15:39
1

@JohnCappelletti TIL: [The JDBC syntax](https://stackoverflow.com/a/56923634/5070879) :) – Lukasz Szozda Jul 07 '19 at 15:40

score 2 · Answer 2 · answered Jul 07 '19 at 16:14

The problem is that the square braces represent character classes in SQL Server like. One method is to use escape. Here are two other options:

xField like '%' + 'DEVCON 5 Minute Epoxy amber [[]1:1]' + '%'
xField like '%' + 'DEVCON 5 Minute Epoxy amber _1:1_' + '%'

The second will match any character, including a square brace. That is probably okay for your purposes.

score 1 · Answer 3 · answered Jul 07 '19 at 15:48

1

Just another option if you want to avoid the need to escape the string is to use charindex()

Example

Select * 
 From  zSyn
 Where charindex('DEVCON 5 Minute Epoxy amber [1:1]',xField)>0

answered Jul 07 '19 at 15:48

John Cappelletti

79,615
7
44
66

russ · Answer 4 · 2019-07-07T15:58:24.247

0

It could be that the square brackets are messing with LIKE because you can sometimes use square brackets for finding numerical or alphabetic characters i.e. LIKE ([A-Z])

SELECT * FROM zSyn WHERE xField LIKE ('%DEVCON 5 Minute Epoxy amber %1:1%')

edited Jul 07 '19 at 15:58

answered Jul 07 '19 at 15:51

russ

579
3
7

Sql where statement with = works but LIKE does not

4 Answers4