SQL LIKE Operator

The SQL LIKE operator is used to comparing a value to other column values using wildcards.

The LIKE operator is used in a WHERE clause to search for a pattern in a column.

There are two wildcards used in conjunction with the LIKE operator:

• The percent sign % - Matches zero, one, or multiple characters.
• The underscore _ - Matches exactly one character.

Syntax

The syntax of the LIKE operator is as follows:

SELECT column_list
FROM table_name 
WHERE column LIKE pattern;

Parameters:

• column_list: List of the columns that you want to select.
• table_name: The name of the table that you want to select data from.
• colunm: The column that you want to match the pattern with.
• pattern: A character expression that contains pattern matching. You can choose from two wildcards: the percent sign % and the underscore _.

Note: The percent sign % and the underscore _ can be used in the same pattern.

The following table shows some example of how to use the LIKE operator with wildcards:

Demo Table

To understand more the LIKE operator, let us consider that we have a table called "Employee" with the following records:

Using the percent sign ( % ) Wildcard with the LIKE Operator - Example

The percent sign % wildcard matches any string of any length, even zero length.

The following SQL statement will find all records in the "Employee" table where the "first_name" starts with "R".

SELECT * 
FROM employee
WHERE firs_name LIKE 'R%';

After the execution, the result set will be as follows:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1009   | Ryan        | Perez     | 1998-10-10 | 2019-10-08 | 8600      | Development      |
+--------+-------------+-----------+------------+------------+-----------+------------------+

As you can see, the rows for the first names "Robert" and "Ryan" have been returned.

Note: In general, the LIKE operator is case-insensitive. For example, in MySQL nonbinary text (TEXT, CHAR, VARCHAR) comparisons are case-insensitive, while binary text (BLOB, BINARY, BINARY) comparisons are case-sensitive.

Because the LIKE operator is case-insensitive, the following SQL statement will return the same results as above:

SELECT * 
FROM employee
WHERE firs_name LIKE 'r%';

Using the underscore ( _ ) Wildcard with the LIKE Operator - Example

The underscore _ wildcard matches exactly one character.

The following SQL statement will find all records in the "Employee" table where the "first_name" containing ar and start and ends with at most one character.

SELECT *
FROM employee 
WHERE first_name LIKE '_ar_';

After running the above command, the output will be as follows:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1011   | Mary        | Garcia    | 2001-03-23 | 2014-03-18 | 8200      | Research         |
+--------+-------------+-----------+------------+------------+-----------+------------------+

Using the NOT Operator with the LIKE Operator - Example

Here, let us see ab example of how to use the NOT operator in conjunction with the LIKE operator.

The following SQL statement will select all employees whose last name does NOT contain the letter "a":

SELECT * 
FROM employee 
WHERE last_name NOT LIKE '%a%';

The output will be as follows:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1006   | James       | Johnson   | 2001-04-30 | 2014-02-18 | 8000      | Marketing        |
| 1007   | Stephanie   | Miller    | 1998-01-17 | 2009-12-12 | 8500      | Human Resources  |
| 1008   | Nelson      | Lopez     | 1999-05-26 | 2010-10-06 | 7300      | Marketing        |
| 1009   | Ryan        | Perez     | 1998-10-10 | 2019-10-08 | 8600      | Development      |
| 1012   | Erik        | Miller    | 1996-09-27 | 2009-04-12 | 6500      | Sales            |
| 1013   | Brenda      | Lopez     | 1999-08-26 | 2010-05-06 | 7500      | Production       |
+--------+-------------+-----------+------------+------------+-----------+------------------+

Using Escape Characters with the LIKE Operator - Example

The LIKE operator also allows to escape the percent sign % wildcard or the underscore _ wildcard and search for the literal versions instead.

In this example, let us suppose that we have a table called "Name" as follows:

+----+------------------+
| id | full_name        |
+----+------------------+
| 1  | Maria_Martinez   |
| 2  | Andrew Rodriguez |
| 3  | James_Johnson    |
| 4  | Susan Taylor     |
| 5  | Erik Miller      |
+----+------------------+

The following SQL statement will return all rows from the "Name" table where the "full_name" contains the _ literal.

SELECT *
FROM name
WHERE full_name LIKE '%#_%' escape '#';

After the execution, the output will be as follows:

+----+------------------+
| id | full_name        |
+----+------------------+
| 1  | maria_martinez   |
| 3  | james_johnson    |
+----+------------------+

Here we specify the # character as an escape character. The first and the last percent sign % are treated as regular wildcards. The #_ is an escaped %, so it is treated as a literal % value.

Note: It is possible only to specify an escape character as a single character. It is a best practice to use a character that will not often be used in your data as an escape character like #.