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.
Friday, July 4, 2008
special characters in SQL Server
Labels:
SQL Database,
SQL Server,
SQL Server Tips
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment