SQL Server question on LIKE vs =

Question

Why does the 1st query below return no data on SQL Server but the 2nd query below does ? Is this intended to work this way ?

1st query:

select 1

WHERE ('[' LIKE '[');

2nd query:

select 1

WHERE ('[' = '[');

Answer 1

The character [ and character ] are used to specify a range of values.

SELECT * FROM Person.EmailAddress WHERE EmailAddress LIKE 'ken[0-9]%'

If you use a query like below it will fail to return data:

SELECT * FROM Person.EmailAddress WHERE EmailAddress LIKE 'ken[%';

If you enclose the character [ between [ ] it should work you can also use ESCAPE as explained on the article.

SELECT * FROM Person.EmailAddress WHERE EmailAddress LIKE 'ken[[]%';

The behavior is explained the article here.

Answer 2

The actual answer should not include %, so the answer is with the ESCAPE. This will work:

select 1

WHERE ('[' LIKE '\[' ESCAPE '[');