Use Sophia to knock out your gen-ed requirements quickly and affordably. Learn more
×

LIKE Wildcards

Author: Sophia

what's covered
This tutorial explores using the LIKE operator with more complex options through wildcards in two parts:
  1. Combining Wildcards
  2. Complex Comparisons

1. Combining Wildcards

We can add more complexity to the LIKE operator with very specific criteria. For example, if we explore the data in the customer table, you may see a variety of phone numbers in different formats. If we want to find the phone numbers that have the format +1 (###) ###-####, we can use the underscore wildcard (_) to represent each digit:


SELECT * 
FROM customer
WHERE phone like '+1 (___) ___-____';

Query Result Example

We could also use this to find email addresses that have a domain name extension with exactly two characters:


SELECT * 
FROM customer
WHERE email like '%.__';

This would look for any number of characters, followed by the dot and ending with two characters. We can use the wildcards to find values of a certain length with a specific format. For example, if we wanted to find the customers with the last name starting with the letter S and having four characters, we can do the following:


SELECT * 
FROM customer
WHERE last_name like 'S___';


Query Result Example

If we were to expand this to find the customers starting with the letter S and having at least four characters, we can add the % to the end of the clause:


SELECT * 
FROM customer
WHERE last_name like 'S___%';


Query Result Example

We can combine some of these with additional checks. For example, if we wanted to find customers with the last name of nine characters starting with the letter S and ending with the letter r, we would use the following:


SELECT * 
FROM customer
WHERE last_name like 'S_______%r';

Notice that we have seven underscore characters in this case as S and r would count as one character each. The % could be listed in any position in the clause in this case.


2. Complex Comparisons

You can also combine both types of wildcards together to find specific content. For example, we may want to split up a marketing campaign to get all of the email addresses that start with the letter m and have a domain starting with the letter a. One might think you could use the following query:


SELECT * 
FROM customer
WHERE email like 'm%a%';

However, this would return the following result that simply looks for the letter m, then 0 or more characters later, it looks for the letter a, and then can end with 0 or more characters.


Query Result Example

This would return values that begin with the letter m, and then include the letter a at any other point.

With an email, you probably want to have specific character checks with the @ and the . which will change the search criteria. We must include these characters as part of the comparison in a specific order as defined by our problem.


SELECT * 
FROM customer
WHERE email like 'm%@a%.%';

In the parameter, we are looking for the letter m, then any number of characters before the @ sign followed by the letter a. Then any number of characters before the dot (.) followed by any number of characters.


Query Result Example


Video Transcript

try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The LIKE clause allows more complex filtering options of data with wildcards.