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 become 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

3. ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true

ANSI SQL92:

1. %

2. _

3. An ESCAPE character only if specified.

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

3.[specifier]

4.[^specifier]

5.| - either of two alternatives

6.* - repetition of the previous item zero or more times.

7.+ - repetition of the previous item one or more times.

8.() - group items together

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

SQL special-characters SQL-like

Answer 1

For SQL Server:

• % It is used for any string of zero or more characters.

         WHERE title LIKE '%computer%' this code is used to find all the book titles with the word 'computer' anywhere in the book title.

• _ Any single character.  WHERE au_fname LIKE '_ean' this helps us to find all four-letter first names that end with ean such as Sean, Dean, and so on.
• [ ] This bracket is used for any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname LIKE '[C-P]arsen' is used here to find the author's last name that is ending with arsen and starting with any single character between C and P, 'such as Larsen, Karsen, and so on. 

        The characters that are included in the range may vary depending on the sorting rules of the collation in range searches.

• [^] This is used for any single character which is not within the specified range ([^a-f]) or set ([^abcdef]).

         WHERE au_lname LIKE 'de[^l]%' this is used for all author's last name starting with de and where the following letter is not 1.

For more information, you can refer to http://msdn.microsoft.com/en-us/library/ms179859.aspx