This lesson will introduce you to pattern matching in SQL. Specifically, you will learn how to use the SQL LIKE and SQL ILIKE statements.
Introduction to SQL Pattern Matching
We have already seen how to test whether a string is exactly equal to a specified value. For example, the following SQL query we saw earlier in this course would all rows from the
staff table where the staff member’s first name is
WHERE NOT first_name=’Mike’
Pattern matching greatly expands our capability to work with strings in SQL.
Pattern matching is the act of making comparisons against a general pattern in a string. A few examples of pattern matching are:
- Testing a word to see if its first letter is
- Testing a word to see if it ends in
- Testing an email input to see whether it ends in
To perform pattern matching in SQL, we use the LIKE and ILIKE statements in conjunction with special wildcard characters.
We will discuss the wildcare characters first.
Wildcard Characters in SQL
There are two wildcard characters in SQL:
_. They have slightly different functionality.
% character is used to replace any sequence of characters in SQL. For example,
%a would match any word that ends in
_ character is used to replace any _single _character in SQL. For example,
_a would return any two-letter word that ends in
We can combine these wildcard characters with the LIKE and ILIKE statements to create SQL queries. We’ll explore this next.
The SQL LIKE and ILIKE Statements
The SQL LIKE and ILIKE statements are used in conjunction with WHERE clauses to find strings that satisfy certain wildcard characteristics.
The general syntax for a LIKE (or ILIKE) statement is below:
SELECT column_name FROM table_name WHERE string LIKE wildcard_string;
The LIKE and ILIKE statements are functionally equivalent except for one important difference: the LIKE statement is case-sensitive while the ILIKE statement is case-insensitive.
Let’s see a few examples of the LIKE and ILIKE statements in action.
First, let’s write a query that returns the
title of every
film that ends in
To begin, let’s simply select the titles from the
SELECT title FROM film;
Next, we have to add the WHERE and LIKE statements. Since we want all films that end with
a, we will use the
% wildcard character:
SELECT title FROM film WHERE title LIKE '%a';
For our second example, let’s generate a list of all movies that only have a single word in them. This is a more complex example that will require multiple wildcard characters as well as the NOT operator.
To start, let’s again begin by simply generating a list of all of the titles from the
SELECT title FROM film;
Next, we need to figure out how to exclude titles that have a space in them. To do this, we can target all titles that _do not match _the wildcard string
% %. The space character represents the space, while the two
% wildcard characters represent a word on either side of the space.
We can exclude movie titles containing spaces by using the NOT operator, like this:
SELECT title FROM film WHERE title NOT LIKE '% %';
If you are following along in pgAdmin4, you will notice that no movie titles are returned:
This indicates that there are no single-word movie titles in our DVD Rental database.
In this lesson, you learned how to perform pattern recognition in SQL using the LIKE and ILIKE operators. In the next lesson, we will learn how to create SQL IN statements.