Using [] and ESCAPE clause in SQL Server LIKE query

home
database
view story

1 Kicks

Using [] and ESCAPE clause in SQL Server LIKE query(blogs.msdn.com)

submitted by wisemx(8074) 3 years, 5 months ago

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.

add a comment |category: Database |Views: 15
Tweet