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 (___) ___-____';
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___';
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___%';
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.
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.
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.
[MUSIC PLAYING] With like clause there's multiple different ways that we can be able to utilize wild cards, we looked at the percent. The percent indicates zero to many different characters. We also have the underscore that looks at one specific character. So it replaces any single option for one character. As an example, if we're looking for the state.
And we want to look at for two characters. The first one starts with the letter S and the second one could be anything at all. If we run this, we should be able to see that it returns SP in this case here. If we extended this a little bit further to have multiple different options. For example, if we're looking for a country that starts with the letter B, we want to have at least two characters and then replace it with any other character at the end.
We can be able to run that. And you'll see that all these countries will have at least two characters. We can also add an additional characters for example, if you want to have three we added another underscore here. So look for at least two characters, add another one, it'll be three characters. So we have lots of different options to be able to control a number of characters between each different option. And then have a little bit more in terms of capabilities to limit and display that filter based on all these different options.
[MUSIC PLAYING]