SQL LIKE Operator
In this tutorial you will learn how to retrieve the data based on a partial match.
Pattern Matching
So far, you've seen the conditions that identify an exact string, e.g. WHERE name='Lois Lane'. But in SQL you can perform partial or pattern matching too using the LIKE operator.
The LIKE operator provides a measure of pattern matching by allowing you to specify wildcards for one or more characters. You can use the following two wildcard characters:
- The percent sign (
%) — Matches any number of characters, even zero characters. - The underscore (
_) — Matches exactly one character
Here're some examples that show how to use the LIKE operator with 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 put the statements we've discussed above into real use by searching some records.
Consider we've 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, let's say you want to find out all the employees whose name begins with S letter.
Example
Try this code »SELECT * FROM employees
WHERE emp_name LIKE 'S%';After executing the query, you'll get the output 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 nonbinary string (CHAR, VARCHAR, TEXT) comparisons are case-insensitive by default, whereas binary strings (BINARY, VARBINARY, BLOB) comparisons are case-sensitive.
This means that if you search with WHERE name LIKE 'S%', you get all column values that start with S or s (as you can see we've got both "Sarah" and "simons"). However, if you want to make this search case sensitive you can use the BINARY operator as follow:
Example
Try this code »-- Syntax for MySQL Database
SELECT * FROM employees
WHERE BINARY emp_name LIKE 'S%';Now, this statement will return only those employees whose name starts with capital S letter:
+--------+------------------+------------+--------+---------+ | emp_id | emp_name | hire_date | salary | dept_id | +--------+------------------+------------+--------+---------+ | 3 | Sarah Connor | 2005-10-18 | 8000 | 5 | +--------+------------------+------------+--------+---------+
Note: If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation to avoid any performance issue.
Tip: Partial matches are useful when you don't know the exact form of the string for which you're searching. You can also use partial matching to retrieve multiple rows that contain similar strings in one of the table's columns.
