SQL LIKE

The LIKE operator is used to filter the result set based on a string pattern. It is always used in the WHERE clause.

Syntax

The syntax for the LIKE operator is as follows:

{PATTERN} often consists of wildcards. We saw several examples of wildcard matching in the previous section.

Example

We use the following table for our example.

Table Store_Information

We want to find all stores whose name contains 'AN'. To do so, we key in,

Result:

The "%" sign before 'AN' means that there may be 0, 1, or more characters before the pattern 'AN.' The "%" sign after 'AN' means that there may be 0, 1, or more characters after the pattern 'AN.' Out of the four store names, 'LOS ANGELES,' 'SAN DIEGO,' and 'SAN FRANCISCO' all contain this pattern.

Exercises

For these exercises, assume we have a table called User_Sales with the following data:

Table User_Sales

1) 1. Which of the following SQL statement is valid? (There can be more than one answer)
a) SELECT First_Name, Last_Name FROM User_Sales WHERE First_Name LIKE 'A%' Last_Name LIKE 'W%';
b) SELECT First_Name, Last_Name FROM User_Sales WHERE First_Name LIKE 'J%' AND Last_Name LIKE 'W%';
c) SELECT First_Name, Last_Name FROM User_Sales First_Name LIKE 'J%' AND Last_Name LIKE 'W%';
d) SELECT First_Name, Last_Name FROM User_Sales WHERE First_Name LIKE 'J%', Last_Name LIKE 'W%';

2. How many records will be returned by the following query? (Assuming the database is configured to be case-insensitive)
SELECT * FROM User_Sales WHERE Last_Name LIKE '%l_e%';

3. How many records will be returned by the following query? (Assuming the database is configured to be case-insensitive)
SELECT * FROM User_Sales WHERE First_Name LIKE '%a%' OR Last_Name LIKE '%e%';