expr LIKE pat [ESCAPE 'escape_char']

MySQL 8.4

Syntax

MYSQL

SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;
+-----------------------------------------+
| 'ä' LIKE 'ae' COLLATE latin1_german2_ci |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;
+--------------------------------------+
| 'ä' = 'ae' COLLATE latin1_german2_ci |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

Examples

Example

Pattern matching using an SQL pattern. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.

MYSQL

SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;
+-----------------------------------------+
| 'ä' LIKE 'ae' COLLATE latin1_german2_ci |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;
+--------------------------------------+
| 'ä' = 'ae' COLLATE latin1_german2_ci |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

Example 2

The pattern need not be a literal string. For example, it can be specified as a string expression or table column. In the latter case, the column must be defined as one of the MySQL string types (see Section 13.3, “String Data Types”).

MYSQL

SELECT 'David!' LIKE 'David_';
1
SELECT 'David!' LIKE '%D%v%';
1

Example 3

Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:

MYSQL

SELECT 'David!' LIKE 'David\_';
0
SELECT 'David_' LIKE 'David\_';
1

Example 4

In particular, trailing spaces are always significant. This differs from comparisons performed with the = operator, for which the significance of trailing spaces in nonbinary strings (CHAR, VARCHAR, and TEXT values) depends on the pad attribute of the collation used for the comparison. For more information, see Trailing Space Handling in Comparisons.

MYSQL

SELECT 'David_' LIKE 'David|_' ESCAPE '|';
1

Example 5

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

MYSQL

SELECT 'abc' LIKE 'ABC';
1
SELECT 'abc' LIKE _utf8mb4 'ABC' COLLATE utf8mb4_0900_as_cs;
0
SELECT 'abc' LIKE _utf8mb4 'ABC' COLLATE utf8mb4_bin;
0
SELECT 'abc' LIKE BINARY 'ABC';
0

Example 6

% matches any number of characters, even zero characters.

MYSQL

SELECT 10 LIKE '1%';
1

Example 7

_ matches exactly one character.

MYSQL

SELECT filename FROM t1;
+--------------+
| filename     |
+--------------+
| C:           |
| C:\          |
| C:\Programs  |
| C:\Programs\ |
+--------------+

Example 8

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, unless the NO_BACKSLASH_ESCAPES SQL mode is enabled. In that case, no escape character is used.

MYSQL

SELECT filename, filename LIKE '%\\' FROM t1;
+--------------+---------------------+
| filename     | filename LIKE '%\\' |
+--------------+---------------------+
| C:           |                   0 |
| C:\          |                   1 |
| C:\Programs  |                   0 |
| C:\Programs\ |                   1 |
+--------------+---------------------+

SELECT filename, filename LIKE '%\\\\' FROM t1;
+--------------+-----------------------+
| filename     | filename LIKE '%\\\\' |
+--------------+-----------------------+
| C:           |                     0 |
| C:\          |                     1 |
| C:\Programs  |                     0 |
| C:\Programs\ |                     1 |
+--------------+-----------------------+