Filtering on non-exact values with LIKE

In the previous examples, you were able to define your conditions precisely — you knew exactly the order values you wanted to see and the exact names of the countries you wanted to filter. However, in many cases, you wouldn’t be able to define the conditions exactly, and would have to rather filter on values that match more or less your conditions. You can use the LIKE clause for this. Let’s start with an example to see how the LIKE function works.

‍

1select
2    *
3from
4    {{raw.e_commerce_sample.webshop_order_line}}
5where
6    product like '%Cheese%'

The result will look like this:

As you see, this query has identified all the order lines where the word Cheese is included. The % character symbolizes anything so when putting this before and after Cheese it returns everything with anything before and anything after Cheese. In the results, you can see it has returned all the products named something starting with Cheese followed by a name of the specific cheese as well as the product named Pasta because the specific pasta name includes the word Cheese. You can try to remove the anything before % from the query:

1select
2    *
3from
4    {{raw.e_commerce_sample.webshop_order_line}}
5where
6    product like 'Cheese%'

As you see, the pasta product disappears, as you’re no longer asking for both anything before and after Cheese, but only for product names beginning with Cheese and followed by anything:

Note that the like function is case sensitive, so if you changed your WHERE statement to product like Cheese%, it would not return any values, because all the cheese are written with a capital C in the table.

If you want your conditions to not be case-sensitive, there are many ways of going about this. A simple way would be to convert all the letters in the column that searches for the results into lower letters and then also write the condition with lower letters. This would look like this:

1select
2    *
3from
4    {{raw.e_commerce_sample.webshop_order_line}}
5where
6    lower(product) like 'cheese%'

‍

This is asking for all the letters in the column to be converted to lowercase before the search. In that way, the function works independently of how the values in the table are written. Keep in mind that you are not converting the column you get returned, so the result will look exactly the same as before with each word capitalized. The new query only alters the capitalization before it searches in the table, and then returns the original values if the conditions are satisfied.

The % in the like statement searches for any character or an undefined number of characters. If you want to only search for any singular character, you can use _ in your condition. For example, product like C__ese% returns the same results as product like C__ese%.

Exercise 4: Find all the customers with First Names starting with ‘S’

In this exercise, you’ll use the Customers table you started with in the first example of the tutorial. It’s located along with the other tables in the sample e-commerce data set. Try to write a query that returns all the columns in the table, and then all the rows in which the customers' first name starts with S. If you run your query, it should give a result like this:

In SQL there are often many means to an end, so you’ll usually be able to get the required result by writing different variations of the same query. A simple way to solve the exercise above would be to write a query like this:

1select
2    id
3  , first_name
4  , last_name
5  , email
6  , phone
7  , updated_at
8from
9    {{raw.e_commerce_sample.webshop_customer}}
10where
11    first_name like 'S%'

‍