Online College Courses for Credit

+
1 Tutorials that teach LIKE Wildcards
Take your pick:
LIKE Wildcards

LIKE Wildcards

Rating:
(0)
Author: Sophia Tutorial
Description:

Identify the correct result set given a SELECT statement that uses the LIKE operator with wildcards.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

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 (___) ___-____';

File:11206-1150-1.png

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___';


File:11207-1150-2.png

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___%';


File:11208-1150-3.png

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.


File:11210-1150-4.png

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.


File:11209-1150-5.png


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.