Sunday, October 12, 2008

Search for special characters (e.g. %) in SQL Server

There are several characters that have special meaning within a SQL query, for example the percent sign (%) in a LIKE query is a wildcard that essentially means "any number of characters can go here." Likewise, the underscore (_) is a wildcard that says "any single character can go here." So what if you are actually looking for a value that contains a literal percent sign? You will end up with bizarre results if you try the following:

SELECT columns FROM table WHERE
column LIKE '%%%'

Instead, you can try one of the following solutions:

SELECT columns FROM table WHERE
column LIKE '%[%]%'

-- or

SELECT columns FROM table WHERE
column LIKE '%\%%' ESCAPE '\'

The first query 'delimits' the special character with square brackets, telling the engine to treat it as a normal literal character instead of a character with special meaning. The second query uses a custom escape character -- you can use any character you like, just be careful that you aren't also expecting to use it as part of the literal string.

Now, you might be wondering, how do I escape a square bracket? If you have something like this:

SELECT columns FROM table WHERE
column LIKE '%[SQL Server Driver]%'

The results won't be what you expect, because an opening square bracket is considered a special character. Surprisingly, you can avoid this problem in much the same way, by one of the following two queries:

SELECT columns FROM table WHERE
column LIKE '%[[]SQL Server Driver]%'

-- or

SELECT columns FROM table WHERE
column LIKE '%\[SQL Server Driver]%' ESCAPE '\'

You can do this replacement at the ASP side, before passing the string in, or within the SQL Server code itself.

No comments: