List of special characters for SQL LIKE clause

Question

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:

1. %
2. _
3. [specifier] E.g. [a-z]
4. [^specifier]
5. ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true
6. ' characters need to be escaped with ' E.g. they're becomes they''re

MySQL:

1. % - Any string of zero or more characters.
2. _ - Any single character
3. ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true

Oracle:

1. % - Any string of zero or more characters.
2. _ - Any single character
3. ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true

Sybase

1. %
2. _
3. [specifier] E.g. [a-z]
4. [^specifier]

Progress:

1. % - Any string of zero or more characters.

2. _ - Any single character

   Reference Guide here [PDF]

PostgreSQL:

1. % - Any string of zero or more characters.
2. _ - Any single character

ANSI SQL92:

1. %
2. _
3. An ESCAPE character only if specified.

PostgreSQL also has the SIMILAR TO operator which adds the following:

1. [specifier]
2. [^specifier]
3. | - either of two alternatives
4. * - repetition of the previous item zero or more times.
5. + - repetition of the previous item one or more times.
6. () - group items together

The idea is to make this a community Wiki that can become a "One stop shop" for this.

Answer 1

For SQL Server, from http://msdn.microsoft.com/en-us/library/ms179859.aspx :

• % Any string of zero or more characters.
• _ Any single character.
• [ ] Any single character within the specified range ([a-f]) or set ([abcdef]).
• [^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

Answer 2

Sybase :

%              : Matches any string of zero or more characters.
_              : Matches a single character.
[specifier]    : Brackets enclose ranges or sets, such as [a-f] 
                 or [abcdef].Specifier  can take two forms:

                 rangespec1-rangespec2: 
                   rangespec1 indicates the start of a range of characters.
                   - is a special character, indicating a range.
                   rangespec2 indicates the end of a range of characters.

                 set: 
                  can be composed of any discrete set of values, in any 
                  order, such as [a2bR].The range [a-f], and the 
                  sets [abcdef] and [fcbdae] return the same 
                  set of values.

                 Specifiers are case-sensitive.

[^specifier]    : A caret (^) preceding a specifier indicates 
                  non-inclusion. [^a-f] means "not in the range 
                  a-f"; [^a2bR] means "not a, 2, b, or R."

Answer 3

ANSI SQL92:

• %
• _
• an ESCAPE character only if specified.

It is disappointing that many databases do not stick to the standard rules and add extra characters, or incorrectly enable ESCAPE with a default value of ‘\’ when it is missing. Like we don't already have enough trouble with ‘\’!

It's impossible to write DBMS-independent code here, because you don't know what characters you're going to have to escape, and the standard says you can't escape things that don't need to be escaped. (See section 8.5/General Rules/3.a.ii.)

Thank you SQL! gnnn

Answer 4

You should add that you have to add an extra ' to escape an exising ' in SQL Server:

smith's -> smith''s

Answer 5

Thank You Very Much