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.

StatementMeaningValues Returned
WHERE name LIKE 'Da%'Find names beginning with DaDavid, Davidson
WHERE name LIKE '%th'Find names ending with thElizabeth, Smith
WHERE name LIKE '%on%'Find names containing the onDavidson, Toni
WHERE name LIKE 'Sa_'Find names beginning with Sa and is followed by at most one characterSam
WHERE name LIKE '_oy'Find names ending with oy and is preceded by at most one characterJoy, Roy
WHERE name LIKE '_an_'Find names containing an and begins and ends with at most one characterDana, Hans
WHERE name LIKE '%ar_'Find names containing ar, begins with any number of characters, and ends with at most one characterRichard, Karl
WHERE name LIKE '_ar%'Find names containing ar, begins with at most one character, and ends with any number of charactersKarl, 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

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

-- 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.

© 2024 All rights reserved. | Made With 🤍 By The_MAK Team