Types of Filters
Filter numbers and dates with comparison operators, and text with LIKE and its % and _ wildcards.
Apprentice · SQL · MySQL · Updated
You can filter rows in several ways. Which one you use depends on the data type of the column you're filtering.
Numeric and Date filters
Numeric columns can be compared similar to the way you compare numbers in other programming languages.
| Operator | Means |
|---|---|
= |
equal to (unlike Java, SQL uses a single =) |
> |
greater than |
>= |
greater than or equal to |
< |
less than |
<= |
less than or equal to |
-- Get all products that cost $12 or more
SELECT *
FROM products
WHERE unit_price >= 12;
-- Get all orders that were placed on July 8, 2021
SELECT *
FROM orders
WHERE order_date = '2021-07-08';
String filters
The LIKE operator is used in SQL to search for a specified pattern in a column. It is often used with wildcards to perform flexible searches within string data. Here are the primary wildcards used with the LIKE operator:
%(percent sign) represents zero, one, or multiple characters._(underscore) represents a single character.
Finding Values That Start with a Specific Letter
SELECT * FROM employees
WHERE first_name LIKE 'J%';
This query returns all employees whose first name starts with the letter 'J'.
Finding Values That End with a Specific Letter
SELECT * FROM products
WHERE product_name LIKE '%s';
This query returns all products whose names end with the letter 's'.
Finding Values That Contain a Specific Substring
SELECT * FROM products
WHERE product_name LIKE '%Sauce%';
This query returns every product with Sauce anywhere in its name, such as Northwoods Cranberry Sauce.
Finding Values with a Specific Length
SELECT * FROM customers
WHERE city LIKE '____';
This query returns customers whose city name is exactly four characters long, such as Bern.
Finding Values That Have a Specific Character in a Specific Position
SELECT * FROM employees
WHERE home_phone LIKE '(206) ___-____';
This query returns the employees whose home phone has area code 206, followed by any three characters, a dash and any four characters. _ matches any one character, so the pattern checks the shape of the value, not that the characters are digits.
Combining Multiple Wildcards
SELECT * FROM customers
WHERE company_name LIKE '% the %';
This query returns companies with the word 'the' in the middle of the name, such as Around the Horn: any characters, a space, 'the', a space, and then any characters again.
Do it: find the companies that start with Fr
A customer calls and remembers only the start of the company's name on her order: "Fr-something". The clerk wants every customer whose company name starts with Fr, to read the short list back to her.
The customers table (the Tables tab shows the SQL that makes it) has these rows:
| company_name | country |
|---|---|
| FISSA Fabrica Inter. Salchichas S.A. | Spain |
| Folies gourmandes | France |
| Folk och fä HB | Sweden |
| France restauration | France |
| Franchi S.p.A. | Italy |
| Frankenversand | Germany |
| Furia Bacalhau e Frutos do Mar | Portugal |
For example, France restauration starts with Fr, so it's on the list. Furia Bacalhau e Frutos do Mar has Fr in its name, in Frutos, but the name doesn't start with it, so it isn't.
Your query should give exactly these rows, in any order:
| company_name | country |
|---|---|
| France restauration | France |
| Franchi S.p.A. | Italy |
| Frankenversand | Germany |
Your job: add a filter to the query in the box so it gives the short list.
- Keep its two columns:
company_nameandcountry. - Keep only the rows whose
company_namestarts withFr, whatever comes after.
Practiced live on the lesson page: write the SQL right there and check it.