MySQL LIKE Operator: A Detailed Guide

A super common task when working with databases is searching for a text string  matching a given pattern. You might need to get all customers whose last names start with a “B” or retrieve all products whose ids contain a certain numerical sequence. If MySQL is your database, then the MySQL LIKE operator is the solution you need.

This post is a guide to using the LIKE operator in MySQL. We’ll explain what exactly the MySQL LIKE operator, and what you need it for and show several practical usage examples. Before we get started, let’s review some requirements for the post.

Prerequisites

For this post, I assume you have some familiarity with the SQL language. You should be fine if you have experience with some relational database, even if it’s not MySQL.

You’ll need a local MySQL instance running and a way to connect to that instance to run commands. I recommend using the CoderPad MySQL Sandbox to quickly and easily get started writing SQL as it’ll be your MySQL client for this article. 

MySQL LIKE operator: The what and why

Let’s start with the basics. What is the LIKE operator in MySQL, and why would you need it?

MySQL LIKE (and like in other database engines as well) is an SQL operator you use to match textual columns against certain patterns. You’ve already seen some examples in the introduction, but here’s a longer list of potential use cases for the LIKE operator:

• Retrieving all phone numbers that start with a given country calling code
• Getting all email addresses that belong to a given domain
• Retrieving all users who live at a certain street or district

In short, you’d typically use this operator when you need to retrieve potentially many rows that all have something in common, and such rows can’t be obtained through the use of the comparison operators (>, <, ==, !=, and so on) or clauses such as BETWEEN.

MySQL LIKE operator: some basic examples

In your sandbox MySQL instance, run the following commands to create a database, select it and create a table:

CREATE TABLE students (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(250) NOT NULL, last_name VARCHAR(250) NOT NULL);Code language: SQL (Structured Query Language) (sql)

For the next steps, let’s add some students:

INSERT INTO students (first_name, last_name) VALUES ('John', 'Smith');

INSERT INTO students (first_name, last_name) VALUES ('Pamelia', 'Black');

INSERT INTO students (first_name, last_name) VALUES ('Jonathan', 'Grey');Code language: SQL (Structured Query Language) (sql)

Now for the example. Let’s retrieve the id and first_name of students whose names start with “Jo”:

SELECT id, first_name FROM students WHERE first_name LIKE 'Jo%';Code language: SQL (Structured Query Language) (sql)

The command above results in the following output:

+----+------------+
| id | first_name |
+----+------------+
|  1 | John       |
|  2 | Jonathan   |
+----+------------+Code language: SQL (Structured Query Language) (sql)

Some things to note for this first example:

• The LIKE operator is used alongside the WHERE clause, in the condition portion
• To filter for values that start with a given text, we specify the text followed by the % wildcard, which is a placeholder for “everything.”

Let’s now retrieve all students whose first name contains the letter “a”, in any position:

SELECT id, first_name FROM students WHERE first_name LIKE '%a%';Code language: SQL (Structured Query Language) (sql)

Now, as you can see, the wildcard is put before and after the letter. And here’s the result:

+----+------------+
| id | first_name |
+----+------------+
|  2 | Jonathan   |
|  3 | Pamelia    |
+----+------------+Code language: SQL (Structured Query Language) (sql)

That’s enough for you to get the gist of it. Let’s see more examples.

Matching a Single Character

All of the examples you saw until now use the % character as a wildcard. This wildcard matches any number of characters. There’s another wildcard that matches exactly one character: the underscore (_) wildcard. Let’s see in practice how it works.

Let’s insert two more students into the table:

INSERT INTO students (first_name, last_name) VALUES ('Joe', 'Martin');

INSERT INTO students (first_name, last_name) VALUES ('Jon', 'Mendel');Code language: SQL (Structured Query Language) (sql)

Would it be possible to write a query to retrieve Joe and Jon, but not John or Jonathan? Yes:

SELECT id, first_name FROM students WHERE first_name LIKE 'Jo_';Code language: SQL (Structured Query Language) (sql)

The query above gets students whose first name starts with “Jo”, followed by exactly one character. Here’s the result:

+----+------------+
| id | first_name |
+----+------------+
|  4 | Joe        |
|  5 | Jon        |
+----+------------+Code language: SQL (Structured Query Language) (sql)

A Negative Match

What if you want to retrieve values that don’t match a given pattern? That’s also possible using the NOT LIKE clause. For instance, let’s say you want to retrieve students whose last names don’t start with an M. Here’s the query for that:

SELECT * FROM students WHERE last_name NOT LIKE 'M%';Code language: SQL (Structured Query Language) (sql)

The query above returns the students with last names “Black”, “Grey”, and “Smith”.

Escaping The Wildcard

What if the pattern you want to match contains the character “%” or “_”? In this case, it’s possible to escape the wildcard. Start by inserting a new student:

INSERT INTO students (first_name, last_name) VALUES ('WeirdName%', '_WeirderLastName');Code language: SQL (Structured Query Language) (sql)

Now, let’s try to match the last name:

SELECT * FROM students WHERE last_name LIKE '_%';Code language: SQL (Structured Query Language) (sql)

If you ran the query above, you’ve seen that it gets all rows. Not what we wanted. Trying to match the first name will result in the same result.

Fortunately, it’s easy to escape the offending character using a backslash, which is the default escaping character. Let’s rewrite the query above in the correct way:

mysql> SELECT * FROM students WHERE last_name LIKE '\_%';

+----+------------+------------------+
| id | first_name | last_name        |
+----+------------+------------------+
|  6 | WeirdName% | _WeirderLastName |
+----+------------+------------------+Code language: SQL (Structured Query Language) (sql)

I already include the results above. As you can see, adding the backslash tells MySQL to treat the underscore just like a normal character. Now, for the first name:

mysql> SELECT * FROM students WHERE first_name LIKE '%\%';

+----+------------+------------------+
| id | first_name | last_name        |
+----+------------+------------------+
|  6 | WeirdName% | _WeirderLastName |
+----+------------+------------------+Code language: SQL (Structured Query Language) (sql)

In this case, the order is different. Since the student’s name ends with the percent character, first we include the wildcard and then the actual percentage character we’re trying to match, preceded by the escape character. As you can see above, it works like a charm.

Finally, even though the backslash is the default escaping character, it’s not the only one. Actually, you can pick your own escaping character using the ESCAPE instruction. Let’s rewrite the query to retrieve match the last name, but specify the “#” character as the escape character:

SELECT * FROM students WHERE last_name LIKE '#_%' ESCAPE '#';Code language: SQL (Structured Query Language) (sql)

And the result:

+----+------------+------------------+
| id | first_name | last_name        |
+----+------------+------------------+
|  6 | WeirdName% | _WeirderLastName |
+----+------------+------------------+Code language: SQL (Structured Query Language) (sql)

Test out the LIKE operator in the sandbox below:

Conclusion

In this post, we’ve covered the MySQL LIKE operator. As you’ve seen, this operator can be used to retrieve values from textual columns which match a given pattern. We’ve walked you through several examples, in which you’ve learned:

• That there are two wildcards you can use with LIKE: the % character, which matches any number of characters, and the _ character, which makes exactly one character
• It’s possible to escape the wildcards using the default escape character
• It’s also possible to specify a different escape character using the ESCAPE clauses

Where should you go from now? Here are some suggestions:

• Learn about potential alternatives to LIKE, such as the REGEXP operator
• Research about how using LIKE on indexed columns can affect query performance
• Try to learn about scenarios in which LIKE might not be the best option, such as when implementing a general search feature (full-text search) for your website

Also, feel free to explore the CoderPad blog for more database-oriented posts, such as these:

• SQL Functions and Techniques Every Data Person Should Know
• Optimize MySQL Database Schema

Thanks for reading, and until the next time.

This post was written by Carlos Schults. Carlos is a consultant and software engineer with experience in desktop, web, and mobile development. Though his primary language is C#, he has experience with a number of languages and platforms. His main interests include automated testing, version control, and code quality.