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

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.

Example

We use the following table for our example.

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

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

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

Next: SQL ORDER BY




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