MySQL Tutorial/String Functions/LIKE

Материал из SQL эксперт
Перейти к: навигация, поиск

expr LIKE pat [ESCAPE "escape_char"]

Pattern matching using SQL simple regular expression comparison.

Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.

With LIKE you can use the following two wildcard characters in the pattern:

Character Description % Matches any number of characters, even zero characters _ Matches exactly one character



   <source lang="sql">

mysql> mysql> SELECT "ABCDE!" LIKE "ABCDE_"; +------------------------+ | "ABCDE!" LIKE "ABCDE_" | +------------------------+ | 1 | +------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT "ABCDE!" LIKE "%A%C%"; +-----------------------+ | "ABCDE!" LIKE "%A%C%" | +-----------------------+ | 1 | +-----------------------+ 1 row in set (0.00 sec)</source>


In MySQL, LIKE is allowed on numeric expressions.

   <source lang="sql">

mysql> mysql> SELECT 10 LIKE "1%"; +--------------+ | 10 LIKE "1%" | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql></source>


String comparisons are not case sensitive unless one of the operands is a binary string

   <source lang="sql">

mysql> mysql> SELECT "abc" LIKE "ABC"; +------------------+ | "abc" LIKE "ABC" | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT "abc" LIKE BINARY "ABC"; +-------------------------+ | "abc" LIKE BINARY "ABC" | +-------------------------+ | 0 | +-------------------------+ 1 row in set (0.00 sec) mysql></source>


To specify a different escape character, use the ESCAPE clause:

   <source lang="sql">

mysql> mysql> SELECT "ABCDE_" LIKE "ABCDE|_" ESCAPE "|"; +------------------------------------+ | "ABCDE_" LIKE "ABCDE|_" ESCAPE "|" | +------------------------------------+ | 1 | +------------------------------------+ 1 row in set (0.00 sec) mysql></source>


To test for literal instances of a wildcard character, precede it by the escape character.

If you do not specify the ESCAPE character, "\" is assumed.

To search for "\n", specify it as "\\n".

To search for "\", specify it as "\\\\".

String Description \% Matches one "%" character \_ Matches one "_" character



   <source lang="sql">

SELECT "ABCDE!" LIKE "ABCDE\_"; SELECT "ABCDE_" LIKE "ABCDE\_";</source>