Like In SQL - sql - sql tutorial - learn sql
- The LIKE operator is used to filter the result set based on a string pattern. It is always used in the WHERE clause. The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator.
- The percent sign (%)
- The underscore (_)
- The percent sign represents zero, one or multiple characters.
- The underscore represents a single number or character.
- These symbols can be used in combinations.

Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialSyntax
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.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExample
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.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialInterview for LIKE COMMAND IN SQL
Interview for LIKE COMMAND IN SQL
- 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 |
- 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%';
- Answer: b
- 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%';
- Answer: 2 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 |
- 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%';
- Answer: 5 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 |
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 |
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialSQL Like Sample Application
SQL Like Sample Application
