What's the complete listing of all special figures for any SQL (I am thinking about SQL Server but other peoples could 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. ' figures have to be steered clear of with ' E.g. they are becomes they''re

MySQL:

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

Oracle:

  1. % - Any string of zero or even more figures.
  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 even more figures.
  2. _ - Any single character

    Reference Guide here [PDF]

PostgreSQL:

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

ANSI SQL92:

  1. %
  2. _
  3. A Getaway character only when specified.

PostgreSQL also offers the SIMILAR TO operator which adds the next:

  1. [specifier]
  2. [^specifier]
  3. | - either of two options
  4. * - repeating the prior item zero or even more occasions.
  5. + - repeating the prior item a number of occasions.
  6. () - group products together

The concept would be to get this to a residential area Wiki that may be a "One-stop shop" with this.

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

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

ANSI SQL92:

  • %
  • _
  • a getaway character only when specified.

It's disappointing that lots of databases don't stay with the conventional rules and add extra figures, or improperly enable ESCAPE having a default worth of ‘’ when it's missing. Like we do not curently have enough challenge with ‘’!

You can't really write DBMS-independent code here, because you do not know what figures you are going to need to escape, and also the standard states you cannot escape stuff that don't have to be steered clear of. (See section 8.5/General Rules/3.a.ii.)

Thanks SQL! gnnn