MySQL LIKE operator

In MySQL, the LIKE operator can be used to test whether a specified pattern matches a string.

MySQL LIKE syntax

The LIKE operator is a binary comparison operator, it requires two operands. Here is the syntax of the the LIKE operator:

expression LIKE pattern

Here:

expression can be a column name, value, or expression (such as function calls).
pattern is a text pattern. You can use two wildcards (% and _) in this pattern.
- % matches zero or more arbitrary characters.
- _ matches any single character.
If you need to match a wildcard, you need to use the escape character \, such as \% and \_.
The pattern is not case-sensitive.
If expression matches pattern, the LIKE operator returns 1. Otherwise, it returns 0.

for example:

a% matches any string starts with a, such as a, ab, abc.
%a matches any string ends with a, such as a, ba, nba.
%a% matches any string includes a, such as a, abc, nba, bac.
%a%b% matches any string includes a, and a is ahead of b, such as ab, 1a1b1.
a_ matches any string starts a and the length of string is 2.
_a matches any string end a and the length of string is 2.

MySQL LIKE algorithm

The LIKE operator is used for fuzzy matching. Its operation rules are as follows:

If the operand on the right side of LIKE matches the left side operand, it returns 1. Otherwise, it returns 0.

SELECT
  'a' LIKE 'a',
  'a' LIKE 'a%',
  'ab' LIKE 'a%',
  'ab' LIKE '%a';

+--------------+---------------+----------------+----------------+
| 'a' LIKE 'a' | 'a' LIKE 'a%' | 'ab' LIKE 'a%' | 'ab' LIKE '%a' |
+--------------+---------------+----------------+----------------+
|            1 |             1 |              1 |              0 |
+--------------+---------------+----------------+----------------+

SELECT
  'a' LIKE 'a_',
  'ab' LIKE 'a_',
  'abc' LIKE 'a_';

+---------------+----------------+-----------------+
| 'a' LIKE 'a_' | 'ab' LIKE 'a_' | 'abc' LIKE 'a_' |
+---------------+----------------+-----------------+
|             0 |              1 |               0 |
+---------------+----------------+-----------------+

If any one operand is NULL, LIKE returns NULL.

SELECT NULL LIKE 'a%', 'a' LIKE NULL;

+----------------+---------------+
| NULL LIKE 'a%' | 'a' LIKE NULL |
+----------------+---------------+
|           NULL |          NULL |
+----------------+---------------+

MySQL LIKE examples

In the following examples, we will use the actor and category tables in the Sakila sample database as demonstrations.

Example 1

The following SQL statement filter all actors whose first_name starts with P. For example: PARKER.

SELECT * FROM actor WHERE first_name LIKE 'P%';

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|       46 | PARKER     | GOLDBERG  | 2006-02-15 04:34:33 |
|       54 | PENELOPE   | PINKETT   | 2006-02-15 04:34:33 |
|      104 | PENELOPE   | CRONYN    | 2006-02-15 04:34:33 |
|      120 | PENELOPE   | MONROE    | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

Example 2

The following SQL statement filter all actors whose first_name ends with ES. For JAMES example:.

SELECT * FROM actor WHERE first_name LIKE '%ES';

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       48 | FRANCES    | DAY-LEWIS | 2006-02-15 04:34:33 |
|       84 | JAMES      | PITT      | 2006-02-15 04:34:33 |
|      126 | FRANCES    | TOMEI     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

Example 3

The following SQL statements filter all actors whose first_name contains AM. For example: JAMES, WILLIAM.

SELECT * FROM actor WHERE first_name LIKE '%AM%';

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       24 | CAMERON    | STREEP    | 2006-02-15 04:34:33 |
|       63 | CAMERON    | WRAY      | 2006-02-15 04:34:33 |
|       71 | ADAM       | GRANT     | 2006-02-15 04:34:33 |
|       84 | JAMES      | PITT      | 2006-02-15 04:34:33 |
|      111 | CAMERON    | ZELLWEGER | 2006-02-15 04:34:33 |
|      132 | ADAM       | HOPPER    | 2006-02-15 04:34:33 |
|      175 | WILLIAM    | HACKMAN   | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

Example 4

The wildcard _ matches any single character. The following SQL statement filter all actors whose first_name ends with AY and contains 3 characters.

SELECT * FROM actor WHERE first_name LIKE '_AY';

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       55 | FAY        | KILMER    | 2006-02-15 04:34:33 |
|       64 | RAY        | JOHANSSON | 2006-02-15 04:34:33 |
|      147 | FAY        | WINSLET   | 2006-02-15 04:34:33 |
|      156 | FAY        | WOOD      | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

Example 5: NOT LIKE

The following SQL statement filter all categories which have a name that does not start with A.

SELECT * FROM category WHERE name NOT LIKE 'A%';

Note, we use NOT LIKE in this statement.

+-------------+-------------+---------------------+
| category_id | name        | last_update         |
+-------------+-------------+---------------------+
|           3 | Children    | 2006-02-15 04:46:27 |
|           4 | Classics    | 2006-02-15 04:46:27 |
|           5 | Comedy      | 2006-02-15 04:46:27 |
|           6 | Documentary | 2006-02-15 04:46:27 |
|           7 | Drama       | 2006-02-15 04:46:27 |
|           8 | Family      | 2006-02-15 04:46:27 |
|           9 | Foreign     | 2006-02-15 04:46:27 |
|          10 | Games       | 2006-02-15 04:46:27 |
|          11 | Horror      | 2006-02-15 04:46:27 |
|          12 | Music       | 2006-02-15 04:46:27 |
|          13 | New         | 2006-02-15 04:46:27 |
|          14 | Sci-Fi      | 2006-02-15 04:46:27 |
|          15 | Sports      | 2006-02-15 04:46:27 |
|          16 | Travel      | 2006-02-15 04:46:27 |
+-------------+-------------+---------------------+

Conclusion

This article described MySQL LIKE operator syntax and its usage. The main points of LIKE operator are as follows:

The LIKE operator is a binary operator.
The LIKE Operator can be used to test whether a text pattern matches a text value.
% matches zero or more arbitrary characters.
_ matches a single arbitrary character.
The text pattern in LIKE expression is not case-sensitive.