SQL LIKE Statement and Operator

The LIKE operator is used to search for a specified pattern in a column.
SQL LIKE Syntax:

SELECT column_name(s) 
FROM table_name 
WHERE column_name LIKE pattern;

The following SQL statement selects all customers with a City starting with the letter "s":

SELECT * FROM Customers 
WHERE City LIKE 's%';

or on MS Access:

SELECT * FROM Customers 
WHERE City LIKE 's*';

The following SQL statement selects all customers with a Country containing the pattern "land":

SELECT * FROM Customers 
WHERE Country LIKE '%land%';

Using the NOT keyword allows you to select records that do NOT match the pattern.
The following SQL statement selects all customers with a Country NOT containing the pattern "land":

SELECT * FROM Customers 
WHERE Country NOT LIKE '%land%';

The LIKE statement is a way to tell SQL that you need to find something, but you do not know exactly what it is, you know only a portion of it. Just indicate the portion of the value that you do know, and let the engine do the rest. Consider if you want to find all rows with a last name of Williams, not something that you may know how to spell off the top of your head. You can use a statement like the following to get back all the rows that are likely candidates:

SELECT lastname FROM Customer 
WHERE lastname LIKE 'Will%'

This tells the engine that you want to get all rows where the Lastname column starts with Will--% indicates "...and ends with any value."
Here's how you would use LIKE with the AND keyword discussed in the previous lesson:

SELECT lastname FROM Customer 
WHERE lastname='Smith' AND ZIPCode like '857%'

The result is a filter for only those names and zip codes that match the criteria. Note that both columns must match to be part of the results set.

List the titles that contain the characters MO, regardless of case. All the letters in the LIKE pattern must be uppercase for this query to work. See Figure 6.4 for the result.

SELECT title_name
FROM titles
WHERE UPPER(title_name) LIKE '%MO%';

title_name
-------------------------
200 Years of German Humor
I Blame My Mother

In the next lesson, you will learn how to place your results into a new table. Before moving on, though, take this brief quiz to assess your knowledge.
Creating SQL Statements - Quiz