MySQL Tutorial/String Functions/LIKE

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

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)

In MySQL, LIKE is allowed on numeric expressions.

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

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

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>

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

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

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

SELECT "ABCDE!" LIKE "ABCDE\_";
SELECT "ABCDE_" LIKE "ABCDE\_";