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_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 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_Name Sales Txn_Date
LOS ANGELES 1500 Jan-05-1999
SAN DIEGO 250 Jan-07-1999
SAN FRANCISCO 300 Jan-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_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) 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 ILIKE

This page was last updated on June 19, 2023.




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