(SQL Tutorials for Citizen Data Scientist)

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.

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.

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:

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

Beginners Guide to SQL – SQL LIKE Operator

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.