Using [] and ESCAPE clause in SQL Server LIKE query


I innocently wrote this SQL SELECT statement to query all rows that have column value started with “[summary]”:


select * from MyTable where Description like ‘[summary]%’ 


Then I was wondering why I could get a lot of rows returned and none of them started with “[summary]”. Hmm, I think I need to look at the SQL Server reference. Well, I only remember the usage of ‘%’ and ‘_’ wildcards in the LIKE clause pattern. The first one will match empty string or any character(s), while the second one is to match any single character only.


Apparently ‘[‘ and ‘]’ characters are special wildcard to match any character within specific range, for example: ‘[a-z]’ to match any character from a to z, ‘[xyz]’ to match ‘x’, ‘y’ or ‘z’ character. So in my example above, it will query all rows with Description column started with either of these characters: ‘s’, ‘u’, ‘m’, ‘a’, ‘r’ or ‘y’. Therefore, I should use ESCAPE clause, and re-write the query statement to be:


select * from MyTable where Description like ‘|[summary|]%’ escape ‘|’


This query now returns the correct result I want.


 

Comments (7)

  1. You’ve been kicked (a good thing) – Trackback from DotNetKicks.com

  2. pundrick says:

    why cant you just write select * from MyTable where Description like 'summary%'

  3. Chris says:

    I know this post is extremely old, but for any future readers. I believe there is still a slight bug here, please correct me if I'm wrong!

    select * from MyTable where Description like '|[summary|]%' escape '|'

    will actually remove the ']' character from the string during comparison; as it is not a special character. Escaping a non special character causes it to be removed during the like comparison. Causing this string to match "[summary%" instead of the intended "[summary]%". Hence why it still works because it still matches the desired result, but it also matches more results than intended!

    To sum it up the ']' character does not need to be escaped!! Here's the fix:

    select * from MyTable where Description like '|[summary]%' escape '|'

  4. Chris says:

    Note with the above comment when using like comparison it does not actually delete the character after the escape character! However the final ']' does not need to be escaped! Although escaping it does absolutely nothing to the end result…

  5. Andy says:

    I found this link very well explained and in detail

    http://www.sqltutorial.us

  6. Charles says:

    I found that rather than escaping the '['  with '|[', escaping worked better with '[[]' (left bracket, left bracket, right bracket).  

    I also did not need to escape the ']'.