Skip navigation

Monthly Archives: July 2009

A few day ago I was looking for a way to search a column for ‘%’ using SQL ‘Like’ predicate. I found 2 ways to do this while reading the book “Microsoft SQL Server 2008 T-SQL fundamentals” by Itzik Ben-Gan published by Microsoft Press.

Solution 1:

If the searched character is a wild card character (“%”, “_”, “[“), then put it in a third bracket.

Example: For searching ‘%’, use column_name LIKE ‘%[%]%’

Note that, for searching “]”, this method cannot be used. For this case, use Solution 2.

Solution 2:

Using this method we need to specify a character as an escape character in front of the character we are searching for, and specify the keyword “ESCAPE” followed by the escape character right after the pattern.

Example: For searching ‘%’, use column_name LIKE ‘%!%%’ ESCAPE ‘!’

This syntax says that the character “!” is an escape character and therefore, the character just after “!” is to be searched. Note that, we need to make sure that the character we are using as an escape character must not appear in the data.

Advertisements