SQL LIKE Tutorial With Practice Exercises
In the last tutorial on we showed you how to check if a row matched a list of values.
In this tutorial, we'll show you how to match on an INFINITE amount of values, using the logical operators and which allow you to filter rows based on whether a string matches a certain pattern. In essence, we'll turn the English statement "find me all medicines whose name starts with "A and end in Y" into the snippet .
SQL LIKE Syntax
Here's the syntax for :
is often accompanied with wildcards and . The can represent zero or multiple characters, and represents a single character.
SQL LIKE Example
For a specific example, imagine you were a Data Analyst who worked at CVS Pharmacy. You have access to a table called pharmacy_sales data:
| product_id | manufacturer | drug | total_sales |
|---|---|---|---|
| 63 | Johnson & Johnson | Pepcid AC Acid Reducer | 2104765.00 |
| 80 | Biogen | Losartan Potassium | 3740527.69 |
| 61 | Biogen | Varicose Relief | 500101.61 |
| 84 | Pfizer | Tranexamic Acid | 2752163.00 |
| 131 | Biogen | ESIKA | 1540350.32 |
| 79 | AstraZeneca | Green Guard Stomach Relief | 3560527.35 |
| 130 | Johnson & Johnson | Medi-First Cold Relief | 3556698.88 |
If you wanted to select all the drugs that had the name "Relief" somewhere in the drug name, you would write this SQL query:
The reason we use the symbol is because it stands for a wild-card, and tells the database to match all the rows that have any characters before "Relief" and any characters after relief – essentially making sure the word "Releif" shows up somewhere in the drug column.
You should get output that looks like this:
Try running the above query yourself against the data in this CVS Pharmacy interview question.
SQL Wildcard Examples
SQL LIKE % Example
The wildcard which represents zero or more characters. For an example use case, imagine your Elon Musk, the CEO of Twitter. One of your employees slightly disagreed with your vision for the product so want to fire them. Sadly, you can't quite remember their name, but are certain it started with "ke" and ended in "y".
Was it Kerry? Kenny? Kelsey?
Here's a SQL query that you which uses the wildcard to search the employee database for our dissenting employee:
SQL LIKE _ Example
The underscore () is used as a wildcard to represent a single character. For example, say you wanted to find all 4 letter words in the dictionary that begin with 'f', and have 'c' as the 3rd letter. You could write:
SQL Wildcard Summary
Don't worry about memorizing each one of the SQL Wildcard patterns below – it's very easy to look this up when you need it. Instead, use this table as a reference!
| Example in Query | Definition |
|---|---|
| Finds any values that starts with "a" | |
| Finds any values that ends with "a" | |
| Finds any values that have "ae" in the middle | |
| Finds any values with "b" in the second position | |
| Finds any values that starts with "a" and ends with "o" | |
| Finds any value that starts with "a" and has 3 characters |
SQL LIKE Practice Questions
Practicing SQL wildcards with is crucial – it's the only way you'll internalize the different types of string patterns you can match against in SQL!
SQL LIKE % Practice
SQL LIKE _ Practice
What's Next
Phew! That was a lot of information for just one command. Next up, we'll take a step back, and review , along with all the other previous commands used to to filter data.
Next Lesson: Filtering Data in SQL Review
Next Lesson
FILTERING REVIEW 🔁