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:

1. 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

2. By default, LIKE operator performs case-insensitive pattern match. See Practice #1.

3. To perform case-sensitive match, use BINARY clause immediately after the keyword LIKE. See Practice #2 and Practice #3.

4. The two pattern matching characters can be combined together in a SELECT statement. See Practice #5.

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

6. You can specify your own escape character by using ESCAPE clause. Put ESCAPE clause after LIKE clause. See Practice #7.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

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.

-- 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:

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:

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:

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:

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:

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:

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:

Other tutorials in this category

1. The Basic SELECT Statement

2. Using Column Alias in SELECT Statement

3. Using Literal Character Strings

4. Perform Arithmetic Operations

5. Use WHERE Clause to Conditionally Select Rows

6. Sorting Data

7. Using LIMIT Clause to Constrain the Number of Rows Retrieved

8. Using Comparison Operators, Part I

9. Using Comparison Operators, Part II

10. Using Logical Operators

11. Eliminating Duplicate Rows

Back to Tutorial Index Page