Skip navigation

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: