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 to check if an attribute’s value matches a specific string pattern using wildcards. 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 will be the most common operator used in the LIKE clause. If we wanted to query the 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 will be 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 may seem like a simpler version of the % operator, but it matches a single wildcard character, unlike the % that has 0 or more. This allows us to query data to look for a specific length of data. 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 may be looking for those 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 it to use the two extra underscores to get 3 characters, it should look like:
SELECT * FROM customer WHERE country LIKE 'U__';
Source: Authored by Vincent Tran