How to Use LIKE Comparison Operator in SQL SELECT Statement for MySQL

Using LIKE Operator

When you do not know the exact value to search for, you can use LIKE operator to perform searches on similar values. The similar values are given a pattern that has to be matched. The pattern matching operation is referred to as wildcard search.

Read Prerequisites for this tutorial and practices if you haven't done so.

Consider the following facts when using LIKE operator in a SELECT statement:

There are two wildcard characters for pattern matching:

Symbol	Character Name	Description
%	Percentage sign	Matches any number of characters, including zero or more characters.
_	Underscore	Matches exactly one character

By default, LIKE operator performs case-insensitive pattern match. See Practice #1.
To perform case-sensitive match, use BINARY clause immediately after the keyword LIKE. See Practice #2 and Practice #3.
The two pattern matching characters can be combined together in a SELECT statement. See Practice #5.
When you need to search for the actual % or _ character, precede it by an escape character. If you do not specify the escape character, backlash (\) is assumed. See Practice #6.

String Description

\% Matches one % character.

\_ Matches one _ character
You can specify your own escape character by using ESCAPE clause. Put ESCAPE clause after LIKE clause. See Practice #7.

Practice #1: Case-insensitive search by using LIKE operator.

Note that to get correct results for Practice #1 and #2, I temporarily changed product name Chai to chai. This is to facilitate the queries to illustrate the difference between case-insensitive and case-sensitive search.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- Query 1: Retrieve all products which product name begins with C
SELECT ProductID, ProductName
FROM products
WHERE ProductName LIKE 'C%';
 
-- Query 2: Use lower-case c for the pattern which returns
-- the same result as Query 1.
SELECT ProductID, ProductName
FROM products
WHERE ProductName LIKE 'c%';

The two queries above display all products which product name begins with character C, regardless of upper-case or lower-case C. Pattern matching character % represents any number of characters after character C or c.

Query result set - both Query 1 and Query 2 returned exact 9 rows:
Use LIKE operator

Practice #2: Case-sensitive search by using LIKE BINARY operator.

-- Retrieve all products which product name begins with C
SELECT ProductID, ProductName
FROM products
WHERE ProductName LIKE BINARY 'C%';

The query above displays all products which product name begins with upper-case character C. Note that product chai is not in the result because it's product name begins with lower-case character c which is eliminated in the result by the clause BINARY.

Query result set - 8 rows returned. Product chai is not in the result:
Use LIKE operator for upper-case string search

Practice #3: Use LIKE operator for lower-case character search.

-- Retrieve all products which product name begins with 
-- lower-case letter c
SELECT ProductID, ProductName
FROM products
WHERE ProductName LIKE BINARY 'c%';

The query above displays all products which product name begins with lower-case character c. Only product chai is returned.

Query result set - 1 row returned:
Use LIKE operator for lower-case string search

Practice #4: Match a single character

-- Retrieve all products which product name begins with 
-- any character and ends with ofu
SELECT ProductID, ProductName
FROM products
WHERE ProductName LIKE '_ofu';

Use pattern matching character _ (underscore) to match any single character.

Query result set - one row returned:
Match a single character

Practice #5: Combine pattern matching characters in one query

/*
Combine pattern matching characters in one query.
 
Character _ is used together with % to find any products 
which name begins with any single character, followed by 
character u, and ends with any number of characters.
*/
SELECT ProductID, ProductName
FROM products
WHERE ProductName LIKE '_u%';

Query result set - 10 rows returned:
Combine two pattern matching characters

Practice #6: Using default escape character \ (backslash)

Before execute this query, change product name Mishi Kobe Niku to Mishi Kobe_Niku and change Queso Cabrales to Queso_Cabrales.

/*
Find the exact match for the actual _ character.
 
Because _ or % character is a pattern matching character,
when we need to have an exact match for the actual _ character 
or % character, precede it by an escape character \ (backlash).
*/
SELECT ProductID, ProductName
FROM products
WHERE ProductName LIKE '%\_%';

Query result set - 2 rows returned:
Escape the actual _ character

Practice #7: Specify your own escape character

/*
Define our own escape character.
 
In this query, & character is defined as 
escape character in ESCAPE clause.
*/
SELECT ProductID, ProductName
FROM products
WHERE ProductName LIKE '%&_%' ESCAPE '&';

Query result set:
Define our own escape character

Next tutorial: Using Logical Operators
Back to Section Index: Single Table SELECT Statement
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search