Search code examples
sqlsql-serversql-like

Square brackets functioning in expression as expected

I am looking for strings that contain this string [management]

Here is a model of scenario:

create table #test (st varchar(200))
insert into #test
values 
('hello'),
('hello management'),
('hello [management]'),
('hello [management] blah'),
('hello [management rev] blah');

select * from #test where st like '%\[management]%' ESCAPE '\'

The above finds what I expect but why does the following not find the same strings?

select * from #test where st like '%[[]management[]]%'
Solution

  • Great question! Instictly it feels like %[[]text[]]% should resolve to:

    ANYTHING[text]ANYTHING.

    But it doesn't. Because ], unlike [, doesn't start a pattern search it does not need escaping. Use %[[]text]% instead.

    For more detail see the "Using Wildcard Characters As Literals" section on the MSDN Like page.