Pages

Friday, July 4, 2008

special characters 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.

No comments: