Programming Notes | Data Engineering | Introduction to SQL

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'

| customer_id | first_name | last_name | email                      | city         | country   | registration_date | credit_limit |
| ----------- | ---------- | --------- | -------------------------- | ------------ | --------- | ----------------- | ------------ |
| 2015        | Olivia     | Rodriguez | [email protected] | Buenos Aires | Argentina | 2023-03-22        | 6800         |

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

| customer_id | first_name | last_name | email                     | city      | country | registration_date | credit_limit |
| ----------- | ---------- | --------- | ------------------------- | --------- | ------- | ----------------- | ------------ |
| 2009        | Ivy        | Anderson  | [email protected]    | Rome      | Italy   | 2023-09-14        | 7000         |
| 2036        | Julia      | Andersson | [email protected] | Stockholm | Sweden  | 2023-12-30        | 8300         |

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

| customer_id | first_name | last_name    | email                      | city         | country     | registration_date | credit_limit |
| ----------- | ---------- | ------------ | -------------------------- | ------------ | ----------- | ----------------- | ------------ |
| 2003        | Carol      | Brown        | [email protected]      | Toronto      | Canada      | 2023-03-10        | 3000         |
| 2005        | Emma       | Miller       | [email protected]      | Paris        | France      | 2023-05-12        | 6000         |
| 2007        | Grace      | Moore        | [email protected]      | Tokyo        | Japan       | 2023-07-22        | 9000         |
| 2008        | Henry      | Taylor       | [email protected]     | Madrid       | Spain       | 2023-08-30        | 5500         |
| 2009        | Ivy        | Anderson     | [email protected]     | Rome         | Italy       | 2023-09-14        | 7000         |
| 2013        | Mia        | Garcia       | [email protected]       | Barcelona    | Spain       | 2023-01-28        | 5200         |
| 2014        | Noah       | Martinez     | [email protected]    | Mexico City  | Mexico      | 2023-02-15        | 3800         |
| 2015        | Olivia     | Rodriguez    | [email protected] | Buenos Aires | Argentina   | 2023-03-22        | 6800         |
| 2023        | Wendy      | Kumar        | [email protected]      | Bangalore    | India       | 2023-11-20        | 5400         |
| 2028        | Bella      | Mueller      | [email protected]    | Vienna       | Austria     | 2023-04-20        | 5600         |
| 2029        | Carlos     | Fernandez    | [email protected] | Madrid       | Spain       | 2023-05-25        | 4700         |
| 2030        | Diana      | Rossi        | [email protected]      | Milan        | Italy       | 2023-06-30        | 7800         |
| 2032        | Fiona      | Martin       | [email protected]     | Brussels     | Belgium     | 2023-08-10        | 5300         |
| 2033        | George     | Van Der Berg | [email protected] | Amsterdam    | Netherlands | 2023-09-15        | 8600         |
| 2034        | Hannah     | Larsson      | [email protected]   | Copenhagen   | Denmark     | 2023-10-20        | 7400         |
| 2036        | Julia      | Andersson    | [email protected]  | Stockholm    | Sweden      | 2023-12-30        | 8300         |
| 2039        | Marcus     | Horvath      | [email protected]   | Budapest     | Hungary     | 2023-03-24        | 6300         |
| 2041        | Oscar      | Petrov       | [email protected]     | Sofia        | Bulgaria    | 2023-05-06        | 3900         |
| 2043        | Quincy     | Stavros      | [email protected]   | Athens       | Greece      | 2023-07-16        | 7100         |
| 2045        | Steve      | Petersen     | [email protected]   | Helsinki     | Finland     | 2023-09-26        | 8100         |
| 2049        | Will       | Berg         | [email protected]        | Bergen       | Norway      | 2023-01-18        | 4600         |

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__%'

| customer_id | first_name | last_name | email                      | city    | country | registration_date | credit_limit |
| ----------- | ---------- | --------- | -------------------------- | ------- | ------- | ----------------- | ------------ |
| 2003        | Carol      | Brown     | [email protected]      | Toronto | Canada  | 2023-03-10        | 3000         |
| 2029        | Carlos     | Fernandez | [email protected] | Madrid  | Spain   | 2023-05-25        | 4700         |

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.