Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

List of special characters for SQL LIKE clause

People also ask

What does like %% mean in SQL?

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.

What is the use of %s in SQL?

%s is a placeholder used in functions like sprintf. Check the manual for other possible placeholders. $sql = sprintf($sql, "Test"); This would replace %s with the string "Test".

What can I use instead of like in SQL?

You may come across the situation, where you need alternate to “like” keyword of SQL, that is to search for sub-string in columns of the table. The one way to achieve it to use instr() function, instr() function takes 3 parameters in account .

Which special characters are not allowed in SQL?

Names can contain (but cannot begin with) the following special characters: 0 through 9, #, @, and $.


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.


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


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

smith's -> smith''s


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