Jaa

Using [] and ESCAPE clause in SQL Server LIKE query

  • Artikkeli

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

  • Anonymous
    August 16, 2008
    PingBack from http://hoursfunnywallpaper.cn/?p=1750

  • Anonymous
    August 16, 2008
    You've been kicked (a good thing) - Trackback from DotNetKicks.com

  • Anonymous
    December 09, 2012
    The comment has been removed

  • Anonymous
    December 14, 2014
    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 '|'

  • Anonymous
    December 15, 2014
    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...

  • Anonymous
    January 17, 2016
    I found this link very well explained and in detail www.sqltutorial.us

  • Anonymous
    February 24, 2016
    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 ']'.