LIKE
So far, we have examples of filtering from columns for specific values
or range of values. However, sometimes we may wish to filter for a
pattern of characters or a specific occurrence of characters within some
text. In instances like these, the operators we have looked at thus far
aren't enough. In this section, we discuss the use of the LIKE operator
and the Wildcard filtering alternatives.
The like operator is used for matching conditions based on patterns the
query is conditioned on. It is usually used for matching text patterns.
Let's begin with the simplest version of the
LIKE operator to match a specific STRING.
Example: Write a query that returns all records of customer whose
first name is 'Olivia'. The query below can get us this result.
SELECT *
FROM customers_new
WHERE first_name LIKE 'Olivia'
As it turns out, only one customer has the first name, Olivia. Now,
suppose we wanted all customers whose first name starts with a specific
letter like 'J'. Here is where WILDCARDS become useful.
WILDCARDS
The LIKE operator uses wildcards for more
robust pattern search to match against complex pattern needs from
character level and beyond. The most commonly used wildcards on SQL are:
- Percentage(%)
- Underscore(_)
Percentage (%) Wildcard
The % wildcard matches any number of characters
to the specified pattern in the LIKE operator. For example, if we are
interested with all customers whose last_name begins with an 'A', we can
use the following query to achieve this.
select *
from customers
where last_name like 'A%'
As you can see from the results, the % wildcard will march every number
of characters after the first letter we specified 'A'.
Now, suppose we wanted to filter for all customers whose last name
contained the letter 'r'. Much like the above case, we will use the
filter '%r%'. Here is what the query would look like:
SELECT *
FROM customers_new
WHERE last_name LIKE '%r%'
Sure enough, we get last names that contain the letter r.
Underscore (_)
Similar to the percentage wildcard, the underscore wildcard is used to
match characters but only one character per underscore. This is often
useful for search across IDs or values that have a unique and consistent
pattern with a predictable variation.
To demonstrate an example of using the
_ wildcard, let's write a query that will
return all customers whose first names starts with C and has at least 3
characters. The query will look like below:
SELECT *
FROM customers
WHERE first_name LIKE 'C__%'
The output contains all of the records with customers within our
conditions. More importantly, the underscore wildcard is looking for not
specific character or number but the existence of a character or numbers
after C.
In the examples above, we have seen the use of the
LIKE operator and Wildcards and how they can be
powerful tools for searching and matching patterns in our data. In the
next section, we continue to look at other useful operators in the SQL
programming language.