Previously, you've seen conditions that identify exact strings, like WHERE name='Lois Lane'. However, SQL also supports partial or pattern matching using the LIKE operator.
The LIKE operator enables pattern matching by allowing you to use wildcards for one or more characters. You can utilize the following wildcard characters:
%) — Matches any number of characters, including zero characters._) — Matches exactly one character.Below are examples demonstrating how to use the LIKE operator with these wildcards.
| Statement | Meaning | Values Returned |
|---|---|---|
WHERE name LIKE 'Da%' |
Find names beginning with Da | David, Davidson |
WHERE name LIKE '%th' |
Find names ending with th | Elizabeth, Smith |
WHERE name LIKE '%on%' |
Find names containing the on | Davidson, Toni |
WHERE name LIKE 'Sa_' |
Find names beginning with Sa and is followed by at most one character | Sam |
WHERE name LIKE '_oy' |
Find names ending with oy and is preceded by at most one character | Joy, Roy |
WHERE name LIKE '_an_' |
Find names containing an and begins and ends with at most one character | Dana, Hans |
WHERE name LIKE '%ar_' |
Find names containing ar, begins with any number of characters, and ends with at most one character | Richard, Karl |
WHERE name LIKE '_ar%' |
Find names containing ar, begins with at most one character, and ends with any number of characters | Karl, Mariya |
Let's apply the statements we've discussed above by performing some record searches.
Imagine we have an employees table in our database with the following records:
+--------+------------------+------------+--------+---------+ | emp_id | emp_name | hire_date | salary | dept_id | +--------+------------------+------------+--------+---------+ | 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 | | 2 | Tony Montana | 2002-07-15 | 6500 | 1 | | 3 | Sarah Connor | 2005-10-18 | 8000 | 5 | | 4 | Rick Deckard | 2007-01-03 | 7200 | 3 | | 5 | Martin Blank | 2008-06-24 | 5600 | NULL | | 6 | simons bistro | 2009-04-01 | 6000 | 1 | +--------+------------------+------------+--------+---------+
Now, suppose you want to find all employees whose names start with the letter 'S'.
SELECT * FROM employees
WHERE emp_name LIKE 'S%';Upon executing the query, the output will resemble something like this:
+--------+------------------+------------+--------+---------+ | emp_id | emp_name | hire_date | salary | dept_id | +--------+------------------+------------+--------+---------+ | 3 | Sarah Connor | 2005-10-18 | 8000 | 5 | | 6 | simons bistro | 2009-04-01 | 6000 | 1 | +--------+------------------+------------+--------+---------+
In MySQL, comparisons of nonbinary strings (CHAR, VARCHAR, TEXT) are case-insensitive by default, while comparisons of binary strings (BINARY, VARBINARY, BLOB) are case-sensitive.
This means that a search with WHERE name LIKE 'S%' will retrieve all column values that start with 'S' or 's' (for example, both "Sarah" and "simons" would be returned). However, if you want to enforce case sensitivity in your search, you can use the BINARY operator as follows:
-- Syntax for MySQL Database
SELECT * FROM employees
WHERE BINARY emp_name LIKE 'S%';Now, this statement will retrieve only those employees whose names start with the uppercase letter 'S':
+--------+------------------+------------+--------+---------+ | emp_id | emp_name | hire_date | salary | dept_id | +--------+------------------+------------+--------+---------+ | 3 | Sarah Connor | 2005-10-18 | 8000 | 5 | +--------+------------------+------------+--------+---------+
Note: If you require a column to always be treated in a case-sensitive manner, declare it with a case-sensitive or binary collation to prevent any performance issues.
Tip: Partial matches are beneficial when you're unsure of the exact form of the string you're searching for. You can also use partial matching to fetch multiple rows that contain similar strings in one of the table's columns.