SQL LIKE operator

In this tutorial, you will learn how to retrieve data based on partial matching.

Pattern matching

So far, you have seen conditions for identifying exact strings, such as WHERE name='Lois Lane'. But in SQL, you can also use the LIKE operator to perform partial or pattern matching.

The LIKE operator allows you to specify wildcard characters for a or more characters to provide pattern matching. You can use the following two wildcards:

• Percent sign (%) - Matches any number of characters, even zero characters.

• Underscore (_) - Exact match of a single character

Here are some examples showing how to use the LIKE operator with wildcards.

By searching through some records, let's put the statement discussed above into practical application.

Consider usemployeesThere is a table in the database that contains the following records:

+--------+------------------+------------+--------+---------+
|　emp_id　|　emp_name　　　　　　　　　|　hire_date　　|　salary　|　dept_id　|
+--------+------------------+------------+--------+---------+
|　　　　　　1　| Ethan Hunt |　2001-05-01　|　　　5000　|　　　　　　　4　|
|　　　　　　2　| Tony Montana |　2002-07-15　|　　　6500 |　　　　　　　1　|
|　　　　　　3　|　Sarah　Connor　　　　　|　2005-10-18　|　　　8000　|　　　　　　　5　|
|　　　　　　4　| Rick Deckard |　2007-01-03　|　　　7200 |　　　　　　　3　|
|　　　　　　5　| Martin Blank |　2008-06-24　|　　　5600 | NULL |
|　　　　　　6　| simons bistro |　2009-04-01　|　　　6000　|　　　　　　　1　|
+--------+------------------+------------+--------+---------+

Now, suppose you want to find all employees whose names start with the letter S.

SELECT　*　FROM employees　
WHERE emp_name LIKE 'S%';

After executing the query, you will get the following output:

+--------+------------------+------------+--------+---------+
|　emp_id　|　emp_name　　　　　　　　　|　hire_date　　|　salary　|　dept_id　|
+--------+------------------+------------+--------+---------+
|　　　　　　3　|　Sarah　Connor　　　　　|　2005-10-18　|　　　8000　|　　　　　　　5　|
|　　　　　　6　| simons bistro |　2009-04-01　|　　　6000　|　　　　　　　1　|
+--------+------------------+------------+--------+---------+

In MySQL, non-binary strings (CHAR, VARCHAR, TEXT) are compared in a case-insensitive manner by default, while binary strings (BINARY, VARBINARY, BLOB) are case-sensitive when compared.

This means that if you use the search WHERE name LIKE 'S%', you will get all column values that start with S or s (as you can see, we have both 'Sarah' and 'simons'). However, if you want to make this search case-sensitive, you can use the BINARY operator in the following way:

--　Syntax for MySQL Database　
SELECT　*　FROM employees　
WHERE BINARY emp_name LIKE 'S%';

Now, this statement will only return employees whose names start with the uppercase letter S:

+--------+------------------+------------+--------+---------+
|　emp_id　|　emp_name　　　　　　　　　|　hire_date　　|　salary　|　dept_id　|
+--------+------------------+------------+--------+---------+
|　　　　　　3　|　Sarah　Connor　　　　　|　2005-10-18　|　　　8000　|　　　　　　　5　|
+--------+------------------+------------+--------+---------+