SQL LIKE

Summary: 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 patternCode language: SQL (Structured Query Language) (sql)

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:

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_characterCode language: SQL (Structured Query Language) (sql)

For example:

value LIKE '%10!%%' ESCAPE '!'Code language: JavaScript (javascript)

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%';Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         105 | David      | Austin    |
|         109 | Daniel     | Faviet    |
+-------------+------------+-----------+Code language: plaintext (plaintext)

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';Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         103 | Alexander  | Hunold    |
|         115 | Alexander  | Khoo      |
|         200 | Jennifer   | Whalen    |
+-------------+------------+-----------+Code language: plaintext (plaintext)

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%';Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+-------------+-----------+
| employee_id | first_name  | last_name |
+-------------+-------------+-----------+
|         102 | Lex         | De Haan   |
|         112 | Jose Manuel | Urman     |
|         123 | Shanta      | Vollman   |
|         178 | Kimberely   | Grant     |
+-------------+-------------+-----------+Code language: plaintext (plaintext)

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__';Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         110 | John       | Chen      |
|         145 | John       | Russell   |
+-------------+------------+-----------+Code language: plaintext (plaintext)

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_';Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+------------+------------+
| 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;Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|         192 | Sarah      | Bell      |
|         117 | Sigal      | Tobias    |
|         100 | Steven     | King      |
|         203 | Susan      | Mavris    |
+-------------+------------+-----------+Code language: plaintext (plaintext)

Summary

• The LIKE operator returns true if a value matches a pattern or false otherwse.
• Use the NOT operator to negate the LIK opeator.
• Use the % wildcard to match one or more characters
• Use the _ wildcard to match a single character.