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 Mike:

SELECT *

FROM staff

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 A
• Testing a word to see if it ends in ion
• Testing an email input to see whether it ends in @hotmail.com or @gmail.com

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: % and _. They have slightly different functionality.

The % character is used to replace any sequence of characters in SQL. For example, %a would match any word that ends in a.

The _ character is used to replace any single _character in SQL. For example, `awould return any two-letter word that ends ina`.

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 a.

To begin, let's simply select the titles from the film table:

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 film table:

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.

Final Thoughts

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.