List of special characters for SQL LIKE clause
special-characters, sql, sql-like
Solution
For SQL Server, from http://msdn.microsoft.com/en-us/library/ms179859.aspx :
% Any string of zero or more characters.
`WHERE title LIKE '%computer%'` finds all book titles with the word 'computer' anywhere in the book title.
_ Any single character.
`WHERE au_fname LIKE '_ean'` finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ] Any single character within the specified range ([a-f]) or set ([abcdef]).
`WHERE au_lname LIKE '[C-P]arsen'` finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation.
[^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]).
`WHERE au_lname LIKE 'de[^l]%'` all author last names starting with de and where the following letter is not l.
Problem
What is the complete list of all special characters for a SQL (I'm interested in SQL Server but other's would be good too) LIKE clause? E.g. ``` SELECT Name FROM Person WHERE Name LIKE '%Jon%' ``` SQL Server: - % - _ - [specifier] E.g. [a-z] - [^specifier] - ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true - ' characters need to be escaped with ' E.g. they're becomes they''re MySQL: - `%` - Any string of zero or more characters. - `_` - Any single character - ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true Oracle: - `%` - Any string of zero or more characters. - `_` - Any single character - ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true Sybase - % - _ - [specifier] E.g. [a-z] - [^specifier] Progress: - `%` - Any string of zero or more characters. `_` - Any single character Reference Guide here [PDF] PostgreSQL: - `%` - Any string of zero or more characters. - `_` - Any single character - ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true ANSI SQL92: - % - _ - An ESCAPE character only if specified. PostgreSQL also has the `SIMILAR TO` operator which adds the following: - `[specifier]` - `[^specifier]` - `|` - either of two alternatives - `*` - repetition of the previous item zero or more times. - `+` - repetition of the previous item one or more times. - `()` - group items together The idea is to make this a community Wiki that can become a "One stop shop" for this.