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

User · Answer

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

User · Answer

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

User · Answer

Potential answer for SQL Server  Interesting I just ran a test using LinqPad with SQL Server which should be just running Linq to SQL underneath and it generates the following SQL statement   Records      Where r    r Name Contains  lkjwer--         -- Region Parameters DECLARE  p0 VarChar 1000      lkjwer--          -- EndRegion SELECT  t0   ID    t0   Name  FROM  RECORDS  AS  t0  WHERE  t0   Name  LIKE  p0 ESCAPE       So I haven t tested it yet but it looks like potentially the ESCAPE     keyword may allow for automatic escaping of a string for use within a like expression

User · Answer

You should add that you have to add an extra   to escape an exising   in SQL Server   smith s -  smith  s

User · Answer

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

[sql] List of special characters for SQL LIKE clause

Examples related to sql

Examples related to special-characters

Examples related to sql-like