How the LIKE Operator Works in MySQL

In MySQL, the LIKE operator performs pattern matching using an SQL pattern.

If the string matches the pattern provided, the result is 1, otherwise it’s 0.

The pattern doesn’t necessarily need to be a literal string. This function can be used with string expressions and table columns.

Syntax

The syntax goes like this:

expr LIKE pat [ESCAPE 'escape_char']

Where expr is the input string and pat is the pattern for which you’re testing the string against.

The optional ESCAPE clause allows you to specify an escape character. The default escape character is \, so you can omit this clause if you don’t need to change this.

Example 1 – Basic Usage

Here’s an example of how to use this operator in a SELECT statement:

SELECT 'Charlie' LIKE 'Char%';

Result:

+------------------------+
| 'Charlie' LIKE 'Char%' |
+------------------------+
|                      1 |
+------------------------+

In this case, the return value is 1 which means that the input string matched the pattern. In particular, we specified that the input string should start with Char and end with anything. The % character matches any number of characters (including zero characters).

Here’s what happens if we drop the %:

SELECT 'Charlie' LIKE 'Char';

Result:

+-----------------------+
| 'Charlie' LIKE 'Char' |
+-----------------------+
|                     0 |
+-----------------------+

The return result is 0 which means no match. This is because we didn’t use a wildcard character to specify any other characters.

Example 2 – The _ Wildcard

We also have the option of using the _ wildcard character to specify only a single character. Here’s an example:

SELECT 'Charlie' LIKE 'Ch_rlie';

Result:

+--------------------------+
| 'Charlie' LIKE 'Ch_rlie' |
+--------------------------+
|                        1 |
+--------------------------+

The two wildcard characters can be combined within a pattern if required:

SELECT 'Charlie likes donuts' LIKE 'Ch_rlie%' AS 'Result';

Result:

+--------+
| Result |
+--------+
|      1 |
+--------+

Here are some more:

SELECT 
  'Charlie likes donuts' LIKE 'Ch_rlie%donuts' AS 'Result 1',
  'Charlie likes donuts' LIKE 'Ch_rlie%nuts' AS 'Result 2',
  'Charlie likes donuts' LIKE 'Ch%rlie %likes %' AS 'Result 3',
  'Charlie likes donuts' LIKE '% likes %' AS 'Result 4';

Result:

+----------+----------+----------+----------+
| Result 1 | Result 2 | Result 3 | Result 4 |
+----------+----------+----------+----------+
|        1 |        1 |        1 |        1 |
+----------+----------+----------+----------+

Let’s make some changes to that example so that we can see some examples of when they don’t match:

SELECT 
  'Charlie likes donuts' LIKE 'Ch%rlie_donuts' AS 'Result 1',
  'Charlie likes donuts' LIKE 'Charlie_nuts' AS 'Result 2',
  'Charlie likes donuts' LIKE 'Charlie _likes donuts' AS 'Result 3',
  'Charlie likes donuts' LIKE '_ likes _' AS 'Result 4';

Result:

+----------+----------+----------+----------+
| Result 1 | Result 2 | Result 3 | Result 4 |
+----------+----------+----------+----------+
|        0 |        0 |        0 |        0 |
+----------+----------+----------+----------+

Example 3 – A Database Example

The LIKE operator is often used within a WHERE clause of a SELECT statement when querying a database. When it’s used in this fashion, it narrows down the results to only those records that match, but we see the actual results (not just a 1 or 0).

Here’s an example of how we can use this operator within a database query:

SELECT ArtistId, ArtistName
FROM Artists
WHERE ArtistName LIKE 'B%';

Result:

+----------+----------------+
| ArtistId | ArtistName     |
+----------+----------------+
|        4 | Buddy Rich     |
|       11 | Black Sabbath  |
|       15 | Birds of Tokyo |
|       16 | Bodyjar        |
+----------+----------------+

In this case, it was a simple query that returns all artists whose names start with the letter B.

Here’s the full list of artists in that table:

SELECT ArtistId, ArtistName
FROM Artists;

Result:

+----------+------------------------+
| ArtistId | ArtistName             |
+----------+------------------------+
|        1 | Iron Maiden            |
|        2 | AC/DC                  |
|        3 | Allan Holdsworth       |
|        4 | Buddy Rich             |
|        5 | Devin Townsend         |
|        6 | Jim Reeves             |
|        7 | Tom Jones              |
|        8 | Maroon 5               |
|        9 | The Script             |
|       10 | Lit                    |
|       11 | Black Sabbath          |
|       12 | Michael Learns to Rock |
|       13 | Carabao                |
|       14 | Karnivool              |
|       15 | Birds of Tokyo         |
|       16 | Bodyjar                |
+----------+------------------------+

Example 4 – Escaping with the Backslash Character

What happens if one of the wildcard characters are in your input string and you need to perform a match against it? You can escape it with the backslash character (\). Here’s an example of such a search with and without the escape character:

SELECT 
  'usr_123' LIKE 'usr_123' AS 'Without escape',
  'usr_123' LIKE 'usr\_123' AS 'With escape';

Result:

+----------------+-------------+
| Without escape | With escape |
+----------------+-------------+
|              1 |           1 |
+----------------+-------------+

In this case, they both matched, but for different reasons. The first line matched because the wildcard specified that any character will match. The second line also matched, but only because the input string happened to have an underscore in the right place.

Let’s change the input string slightly so that we get a different result:

SELECT 
  'usr+123' LIKE 'usr_123' AS 'Without escape',
  'usr+123' LIKE 'usr\_123' AS 'With escape';

Result:

+----------------+-------------+
| Without escape | With escape |
+----------------+-------------+
|              1 |           0 |
+----------------+-------------+

The unescaped version returned positive, because the wildcard meant that we could have any character in that spot. The escaped version explicitly stated that only the underscore character (_) will match. The input string didn’t have an underscore character in that spot and so the result was negative.

Example 5 – The ESCAPE Clause

You can also use the ESCAPE clause to specify your own custom escape character. Here’s an example:

SELECT 
  'usr_123' LIKE 'usr|_123' ESCAPE '|' AS 'String 1',
  'usr+123' LIKE 'usr|_123' ESCAPE '|' AS 'String 2';

Result:

+----------+----------+
| String 1 | String 2 |
+----------+----------+
|        1 |        0 |
+----------+----------+

Example 6 – Numeric Expressions

The MySQL implementation of the LIKE operator allows numeric expressions to be used. Here’s an example:

SELECT 
  1234 LIKE '12%',
  1234 LIKE '12_';

Result:

+-----------------+-----------------+
| 1234 LIKE '12%' | 1234 LIKE '12_' |
+-----------------+-----------------+
|               1 |               0 |
+-----------------+-----------------+