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 <span class="code">LIKE</span> clause for this. Let’s start with an example to see how the <span class="code">LIKE</span> function works.
The result will look like this:
As you see, this query has identified all the order lines where the word <span class="code">Cheese</span> is included. The <span class="code">%</span> character symbolizes anything so when putting this before and after <span class="code">Cheese</span> it returns everything with anything before and anything after <span class="code">Cheese</span>. In the results, you can see it has returned all the products named something starting with <span class="code">Cheese</span> followed by a name of the specific cheese as well as the product named <span class="code">Pasta</span> because the specific pasta name includes the word <span class="code">Cheese</span>. You can try to remove the anything before <span class="code">%</span> from the query:
As you see, the pasta product disappears, as you’re no longer asking for both anything before and after <span class="code">Cheese</span>, but only for product names beginning with <span class="code">Cheese</span> and followed by anything:
Note that the like function is case sensitive, so if you changed your <span class="code">WHERE</span> statement to product like <span class="code">Cheese%</span>, 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:
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 <span class="code">%</span> 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 <span class="code">_</span> in your condition. For example, product like <span class="code">C__ese%</span> returns the same results as product like <span class="code">C__ese%</span>.
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: