SQL LIKE Operator

In this tutorial, you will learn how to use the SQL LIKE operator to test whether a value matches a pattern.

Introduction to SQL LIKE operator

The LIKE operator is one of the SQL logical operators. The LIKE operator returns true if a value matches a pattern or false otherwise.

The syntax of the LIKE operator is as follows:

expression LIKE pattern

In this syntax, the LIKE operator tests whether an expression matches the pattern. The SQL standard provides you with two wildcard characters to make a pattern:

•  % percent wildcard matches zero, one, or more characters
•  _ underscore wildcard matches a single character.

The following show an example of using the % and _ wildcard characters:

Expression	Meaning
LIKE 'Kim%'	match a string that starts with Kim
LIKE '%er'	match a string that ends with er
LIKE '%ch%'	match a string that contains ch
LIKE 'Le_'	match a string that starts with Le and is followed by one character e.g., Les, Len…
LIKE '_uy'	match a string that ends with uy and is preceded by one character e.g., guy
LIKE '%are_'	match a string that contains are and ends with one character
LIKE '_are%'	match a string that contains are, starts with one character, and ends with any number of characters

Note that besides the % and _ wildcards, some database systems may have other wildcard characters that are specific to those databases.

NOT LIKE

To negate the LIKE operator, you use the NOT operator:

expression NOT LIKE pattern

The NOT LIKE operator returns true if the expression doesn’t match the pattern or false otherwise.

Escape character

To match a string that contains a wildcard for example 10%, you need to instruct the LIKE operator to treat the % in 10% as a regular character.

To do that, you need to explicitly specify an escape character after the ESCAPE clause:

expression LIKE pattern ESCAPE escape_character

For example:

value LIKE '%10!%%' ESCAPE '!'

In this example, the ! is an escape character. It instructs the LIKE operator to treat the % in the 10% as a regular character.

In practice, you often use the LIKE operator in  WHERE clause of the SELECT, UPDATE, and DELETE statements.

SQL LIKE operator examples

We’ll use the employees table in the sample database for the demonstration.

The following example uses the LIKE operator to find all employees whose first names start with Da :

SELECT
    employee_id,
    first_name,
    last_name
FROM
    employees
WHERE
    first_name LIKE 'Da%';

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         105 | David      | Austin    |
|         109 | Daniel     | Faviet    |
+-------------+------------+-----------+

The following example use the LIKE operator to find all employees whose first names end with er:

SELECT
    employee_id,
    first_name,
    last_name
FROM
    employees
WHERE
    first_name LIKE '%er';

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         103 | Alexander  | Hunold    |
|         115 | Alexander  | Khoo      |
|         200 | Jennifer   | Whalen    |
+-------------+------------+-----------+

The following example uses the LIKE operator to find employees whose last names contain the word an:

SELECT
    employee_id,
    first_name,
    last_name
FROM
    employees
WHERE
    last_name LIKE '%an%';

+-------------+-------------+-----------+
| employee_id | first_name  | last_name |
+-------------+-------------+-----------+
|         102 | Lex         | De Haan   |
|         112 | Jose Manuel | Urman     |
|         123 | Shanta      | Vollman   |
|         178 | Kimberely   | Grant     |
+-------------+-------------+-----------+

The following statement retrieves employees whose first names start with Jo and are followed by at most 2 characters:

SELECT
    employee_id,
    first_name,
    last_name
FROM
    employees
WHERE
    first_name LIKE 'Jo__';

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         110 | John       | Chen      |
|         145 | John       | Russell   |
+-------------+------------+-----------+

The following statement uses the LIKE operator with the % and _ wildcard to find employees whose first names start with any number of characters and are followed by at most one character:

SELECT
    employee_id,
    first_name,
    last_name
FROM
    employees
WHERE
    first_name LIKE '%are_';

+-------------+------------+------------+
| employee_id | first_name | last_name  |
+-------------+------------+------------+
|         119 | Karen      | Colmenares |
|         146 | Karen      | Partners   |
+-------------+------------+------------+

SQL NOT LIKE operator example

The following example uses the NOT LIKE operator to find all employees whose first names start with the letter S but not start with Sh:

SELECT
    employee_id,
    first_name,
    last_name
FROM
    employees
WHERE
    first_name LIKE 'S%'
AND first_name NOT LIKE 'Sh%'
ORDER BY
    first_name;

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         192 | Sarah      | Bell      |
|         117 | Sigal      | Tobias    |
|         100 | Steven     | King      |
|         203 | Susan      | Mavris    |
+-------------+------------+-----------+