How to Use LIKE in SQL

Problem:

You need to search for specific patterns (certain combinations of letters or numbers) within text data in your database.

We'll show you how to use the LIKE operator in SQL to:

• Find city names starting with S
• Find city names with exactly five characters
• Find city names starting with S and with o as the second-to-last character

You'll also learn how to use LIKE and wildcard operators in SQL to find your own patterns in text data in your database.

Example:

Our database has a table named customer with data in the following columns: id, first_name, last_name, and city.

Let’s see some examples of pattern matching in SQL.

Example 1: Cities starting with S

Let’s display the first name, last name, and city of each customer whose city name starts with the letter 'S'.

Solution:

SELECT last_name, first_name, city  
FROM customer
WHERE city LIKE 'S%';

We used the % wildcard, which represents zero or more unknown characters. This wildcard can be used anywhere in the string.

Example 2: City names with exactly five characters

Let’s show the first name, last name, and city of each customer whose city name contains exactly five characters.

Solution:

SELECT last_name, first_name, city  
FROM customer
WHERE city LIKE '_____';

Here’s the result:

The underscore _ wildcard can be used to represent a single unknown character. Thus, by using five of them in a row, we can represent all strings that are five characters long. In our example, the only city matching that description is Seoul.

Example 3: City names starting with S and with o as the second-to-last character

Let’s show the first name, last name, and city of each customer whose city starts with the letter 'S' and has the letter 'o' as the third-to-last character.

Solution:

SELECT last_name, first_name, city  
FROM customer
WHERE city LIKE 'S%o__';

Here’s the result:

The query returned only two records: Lisa Black from Singapore and Milan Brick from Seoul. These cities match the given pattern. Here, we combined both wildcards to generate a more specific pattern: all strings beginning with a capital letter 'S' and containing any number of other characters after that, so long as the third-to-last character is a lowercase letter 'o'.

Discussion:

If you want to select records in which a string matches a specific pattern, you can use a LIKE clause as the condition in a WHERE clause. After WHERE, list the name of the column (e.g., city) followed by a LIKE clause specifying the string pattern (e.g., 'S%o__') to search for.

As we saw in the examples, you can use two special characters known as wildcards to represent unknown characters:

• The '%' wildcard denotes zero or more characters.
• The '_' wildcard denotes any single character.

You can combine these wildcards to achieve some really powerful results. You can also chain more than one LIKE condition. Finally, you can use the NOT operator to find strings that don’t match the given pattern.