0 votes
1 view
in SQL by (20.3k points)

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

1 Answer

0 votes
by (40.4k points)

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 

Related questions

0 votes
1 answer
asked Nov 21, 2020 in SQL by Appu (6.1k points)
+2 votes
1 answer
0 votes
1 answer
asked Jan 2 in SQL by Appu (6.1k points)
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...