SQL > SQL Commands > Like

LIKE is another keyword that is used in the WHERE clause. Basically, LIKE allows you to do a search based on a pattern rather than specifying exactly what is desired (as in IN) or spell out a range (as in BETWEEN). The syntax is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN};

{PATTERN} often consists of wildcards. We saw several examples of wildcard matching in the previous section. Below we use an example to see how wildcard is used in conjunction with LIKE:

Table Store_Information

Store_NameSalesTxn_Date
LOS ANGELES1500Jan-05-1999
SAN DIEGO250Jan-07-1999
SAN FRANCISCO300Jan-08-1999
BOSTON700Jan-08-1999

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

SELECT *
FROM Store_Information
WHERE Store_Name LIKE '%AN%';

Result:

Store_NameSalesTxn_Date
LOS ANGELES1500Jan-05-1999
SAN DIEGO250Jan-07-1999
SAN FRANCISCO300Jan-08-1999

Exercises

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

Table User_Sales

First_NameLast_NameBirth_DateGenderJoi n_DateTotal_Sales
SophieLeeJan-05-1960FApr-05-2015500
RichardBrownJan-07-1975MApr-05-2015200
JamalSantoOct-08-1983MApr-09-2015350
CaseyHealySep-20-1969MApr-09-201580
JillWilkesNov-20-1979FApr-15-2015210

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 Last_Name LIKE '%a%' OR First_Name LIKE '%e%';

Next: SQL ORDER BY




Copyright © 2015   1keydata.com   All Rights Reserved.     Privacy Policy     About   Contact