SQL: Using the LIKE Operator

In previous posts, I have written about different ways of using SQL, from entering, updating and deleting data to making queries using different filters. In this post, I will write about another type of filter for queries called the LIKE operator.

The LIKE operator is specifically used with the WHEN clause. This operator is used when one is looking for a specific pattern within a column. For an example, we are working in a clients table. This is the table where any personal information like name, birthdate, address and phone number might be found. We might be searching the table for clients with a phone number with a certain area code. The LIKE operator could be used with the WHEN clause to filter the data to return only the clients that have that specific number pattern as the area code.

The formula to a query using the LIKE operator is the following:

SELECT <column1_name>, <column2_name>,..., <columnx_name>
FROM <table_name>
WHERE <columnN> LIKE pattern;

The majority of the code above is as expected. It is nearly identical when making a query using just the WHERE clause. The important factor to consider when using the LIKE clause is the pattern. The observations in the specified column are being compared to this pattern, so the question becomes how do we determine the pattern that we are filtering the data by?

In SQL there is type of symbol called a wildcard. These wildcards allow programmers to create the patterns to search for within observations. The two most common wildcards are the percent symbol and the underscore symbol. There are three other wildcards that are used less frequently.

Let’s go over which each wildcard does.

1. % : The percent symbol represents zero or more unknown characters.
2. _ : The underscore symbol represents a single unknown character.
3. [] : The brackets represent any one character within the brackets.
4. ^ : The caret symbol, when used within the brackets symbol, represents any one character NOT within the brackets.
5. - : The dash symbol, when used within the brackets symbol, represents any one character from a range of characters within the brackets.

As you might expect after reviewing the last three wildcards, different wildcards can be used in combinations within the same pattern.

Let’s return to our previous example of searching for clients with phone numbers containing a specific area code. In this example, we are searching for clients with an area code of 917.

To compose this query, we will first concentrate on the beginning of the query and end with the pattern.

SELECT first_name, last_name, phone_number
FROM clients_table
WHERE phone_number LIKE pattern

So, from this query we want to view the first and last names of the clients along with their phone number. All this information is being taken from the clients table. Next we use the WHERE clause with the LIKE operator to determine if the phone number matches a specific pattern.

Now we can determine how to organize our pattern. It is also important to note how the phone number is entered into the data table such as (917)-XXX-XXXX , 917-XXX-XXXX , or 1–917-XXX-XXXX. There are many possibilities, so it is important to know which format is used in the column. For this case, let us assume the data table is using the first example. We know that a phone number will begin with the area code. So the pattern could be '(917)%' . This means that the phone number we are looking for begins with (917) and has zero or more characters coming after it. So it does not matter what the rest of the phone number is, the query will return with only those phone numbers with the 917 area code.

So the final version of our query would be the following:

SELECT first_name, last_name, phone_number
FROM clients_table
WHERE phone_number LIKE '(917)%'

There is one more item to note while using the LIKE operator, which is that the LIKE operator can be used in combination with other operators such as AND, OR, and NOT. You might have multiple conditions to consider when filtering. For example, you are querying a sql table about books. You are looking for books by a certain author, but you can only remember the first name and part of the last name. So if you were searching for my name and the only part of the last name you remembered was that it ended in bach, then a query might look like the following:

SELECT title, author_fname, author_lname
FROM books
WHERE author_fname = 'Malcolm' AND author_lname LIKE '%bach'

I hope this helps your understanding of using the LIKE operator is SQL queries. Thanks for reading.

For more documentation on the LIKE operator:

MySQL :: MySQL 8.0 Reference Manual :: 3.3.4.7 Pattern Matching