There are special operators that allow us to check if a value is in a range. The LIKE operator in a WHERE clause allows you use wildcards to check if an attribute’s value matches a specific string pattern. There are two different wildcards that can be used with the LIKE operator:
It is important to note that the string text is case sensitive with the content that is not in the wildcards, so the matches will only display if the query entry is the same as what is in the table data.
If we have a string ‘abc’ in a table, the following search terms will return the associated results when compared against 'abc':
As shown above, the % searches for zero, one, or more characters as a replacement. It is the most common operator used in the LIKE clause. If we wanted to query our customer table and list all of the customers whose name starts with the letter L, we would use the % wildcard after the letter L:
SELECT *
FROM customer
WHERE first_name like 'L%';
If we wanted to list all of the customers who have their email in the domain gmail.com, we would have the % wildcard operator before @gmail.com:
SELECT *
FROM customer
WHERE email like '%@gmail.com';
This is the most common way to use the % operator to search for content. In the following tutorial, we’ll dive into more complex ways to work with wildcards.
The underscore (_) operator matches a single wildcard character, unlike the % operator that has 0 or more. This allows us to query data to look for a specific length of data. For example, if we wanted to look for customers that have the state starting with C and having two characters, we can do the following:
SELECT *
FROM customer
WHERE state LIKE 'C_';
Another example is looking customers that live in a country that has three characters and starts with the letter U. If we used the % operator, it would search for 0 or more characters after U:
SELECT *
FROM customer
WHERE country LIKE 'U%';
This would return some extra rows of countries, like the United Kingdom:
If we changed the LIKE clause to use the two extra underscores to get 3 characters, it should look like:
SELECT *
FROM customer
WHERE country LIKE 'U__';
[MUSIC PLAYING] Within columns of text, there's not always a situation where you might know the entire text. You might be looking for a specific pattern, this is where the LIKE clause within the WHERE clause will be very helpful as you can actually control a little bit more of what's actually being returned. For example, if we were looking for all the different customers to have the first name.
Starts with the letter L, we can enter in the letter and then we utilize the percent sign. The percent sign indicates any number of characters from zero to whatever number that's available. And if we enter that, will have it returned with five customers that have the letter L starting with the name.
You can also include multiple character. So for example if we're filling for Lu for the first two characters it'll only return three that have Lu starting in this case. Know you can also set it such that it's the last character that's going to be defined with any kind of wild card values beforehand by having said that the letter S at the very end. If we run that, we'll now see that all these first names have the letter S at the very end.
[MUSIC PLAYING]
Source: Authored by Vincent Tran