Tutorial on SQL LIKE statement

 
LIKE is a keyword which is used in the WHERE clause.
Fundamentally, LIKE permits you to perform a search depending on a pattern instead of specifying exactly what is expected (as in IN) or spell out a range (as in BETWEEN).
 
The syntax is shown below:
 
SELECT “column_name”
FROM “table_name”
WHERE “column_name” LIKE {PATTERN};
 
{PATTERN} often contains wildcards. Below we see an example to explore, how wildcard is actually used in conjunction with LIKE:
 

Table Country_Information

Country_Name Sales Txn_Date
LOS ANGELES 1500 Jan-05-1999
SAN DIEGO 250 Jan-07-1999
SAN FRANCISCO 300 Jan-08-1999
BOSTON 700 Jan-08-1999

 
We wish to search all stores whose name contains ‘AN’. To perform that, we type the following query:
 
SELECT *
FROM Country_Information
WHERE Country _Name LIKE ‘%AN%’;

Outcome:

Country _Name Sales Txn_Date
LOS ANGELES 1500 Jan-05-1999
SAN DIEGO 250 Jan-07-1999
SAN FRANCISCO 300 Jan-08-1999

 

Practice Exercises

For the following exercises, assume that we have a table known as User_Sales with the following given data:
 
Table : User_Sales

First_Name Last_Name Birth_Date Gender Join_Date Total_Sales
Sophie Lee Jan-05-1960 F Apr-05-2015 500
Richard Brown Jan-07-1975 M Apr-05-2015 200
Jamal Santo Oct-08-1983 M Apr-09-2015 350
Casey Healy Sep-20-1969 M Apr-09-2015 80
Jill Wilkes Nov-20-1979 F Apr-15-2015 210

 
1) Find which statement is valid among the following SQL statements? (There could 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, when this query is given? (Let’s assume that database is case-insensitive)
 
SELECT * FROM User_Sales WHERE Last_Name LIKE ‘%l_e%’;
 
3. How many records will be returned, when the following query is given? (Let’s assume the database is case-insensitive)
 
SELECT * FROM User_Sales WHERE First_Name LIKE ‘%a%’ OR Last_Name LIKE ‘%e%’;

Answers are as follows:

1. b)
 
2. Two records are returned. They are,

First_Name Last_Name Birth_Date Gender Join_Date Total_Sales
Sophie Lee Jan-05-1960 F Apr-05-2015 500
Jill Wilkes Nov-20-1979 F Apr-15-2015 210

 
3. Five records are returned. They are,

Country _Name Sales Txn_Date
LOS ANGELES 1500 Jan-05-1999
SAN DIEGO 250 Jan-07-1999
SAN FRANCISCO 300 Jan-08-1999

 

Practice Exercises

For the following exercises, assume that we have a table known as User_Sales with the following given data:
 
Table : User_Sales

First_Name Last_Name Birth_Date Gender Join_Date Total_Sales
Sophie Lee Jan-05-1960 F Apr-05-2015 500
Richard Brown Jan-07-1975 M Apr-05-2015 200
Jamal Santo Oct-08-1983 M Apr-09-2015 350
Casey Healy Sep-20-1969 M Apr-09-2015 80
Jill Wilkes Nov-20-1979 F Apr-15-2015 210

 

Kristin is a content strategist at Techarex Networks. Kristin follows the B2B technology space closely and loves to write on the latest changes in technology, futuretech and fixes for day to day how to issues. Besides writing Kristin also loves music, moves and skating.