---
title: "MySQL LIKE Operator: A Detailed Guide | CoderPad"
description: "A super common task when working with databases is searching for a text string matching a given pattern. This post is a guide to using the LIKE operator in MySQL. We'll explain what exactly is MySQL like and what you need it for."
canonical: "https://coderpad.io/blog/development/mysql-like-operator-a-detailed-guide/"
last_modified: "2026-04-15 16:21:00"
---
[Back to blog](https://coderpad.io/blog/)

# MySQL LIKE Operator: A Detailed Guide

January 6, 2023 2 Minute Read

By Carlos Schults

[Development](https://coderpad.io/blog/development/)

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 [](https://hub.docker.com/_/mysql) [CoderPad MySQL Sandbox](https://coderpad.io/languages/mysql/) 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](https://coderpad.io/blog/development/how-to-use-indexes-to-increase-mysql-database-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](https://coderpad.io/blog/development/sql-functions-and-techniques-every-data-person-should-know/)
- [Optimize MySQL Database Schema](https://coderpad.io/blog/development/optimize-mysql-database-schema/)

Thanks for reading, and until the next time.

This post was written by Carlos Schults. [Carlos](https://carlosschults.net) 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.

## Subscribe for more CoderPad insights

## Trending Articles

Previous slide Next slide [Development

## What is Prompt Engineering?

Introducing AI tools such as ChatGPT and Bard helps increase the efficiency of tasks such as data generation, code generation and completion, text summarization, etc. How were these AI tools trained to answer almost every question you ask them? The answer, prompt engineering, is discussed in this blog post.

Read the post](https://coderpad.io/blog/development/what-is-prompt-engineering/) [Development

## A Guide To Using React’s useCallback() Hook

Are you looking to optimize your React applications? Have you heard of the useCallback hook? useCallback is a powerful React hook that helps you optimize your application by preventing unnecessary re-renders. In this post, we'll dive into React's useCallback() hook, define referential equality and callback functions and how to tie it all together.

Read the post](https://coderpad.io/blog/development/a-guide-to-using-reacts-usecallback-hook/) [Development

## MySQL vs. PostgreSQL: How Do They Compare?

A database is a convenient tool that allows users to access information, maintain records, and apply commonly used operations, such as insertion, modification, deletion, and data organization. Learn about MySQL vs PostgreSQL, their features, advantages, and disadvantages. Finally, check out which one is the best choice for you.

Read the post](https://coderpad.io/blog/development/mysql-vs-postgresql-how-do-they-compare/) [Development

## PostgreSQL LIKE Operator: A Detailed Guide

When using databases, searching for values that match a given pattern is a familiar necessity. Learn how you can retrieve entries with queries including a particular string using Postgres LIKE operator.

Read the post](https://coderpad.io/blog/development/postgresql-like-operator-a-detailed-guide/) [Development

## How To Use DataProvider In TestNG With Examples

When it comes to testing there are many tools and techniques available. In this post, we'll discuss how to use DataProvider with TestNG.

Read the post](https://coderpad.io/blog/development/how-to-use-dataprovider-in-testng-with-examples/) [Development

## How To Get Up And Running With Django Migrations: A Guide

Django migrations are a way of handling the application of changes to a database schema. By following the steps outlined in this guide, you can easily set up and use migrations in your Django projects.

Read the post](https://coderpad.io/blog/development/how-to-get-up-and-running-with-django-migrations-a-guide/) [Development

## Writing A Parameterized Test In JUnit With Examples

A JUnit parameterized test allows you to decouple data from structure, generating cleaner and more reliable tests. Learn more in this guide.

Read the post](https://coderpad.io/blog/development/writing-a-parameterized-test-in-junit-with-examples/) [Development

## How To Use Assert In JUnit With Examples

The JUnit Assert class is what you use to write unit testing assertions in JUnit. Learn more about it in this guide, full of examples.

Read the post](https://coderpad.io/blog/development/how-to-use-assert-in-junit-with-examples/) [Development

## A Simple Guide To TypeORM: What It Is And How To Use It

Considering current languages have object-oriented programming features, most database management systems are based on the relational model, with tables instead of objects. For these reasons, mapping relational objects is very important in development. In this article, you will learn what TypeORM, one of the main tools used today, is.

Read the post](https://coderpad.io/blog/development/a-simple-guide-to-typeorm-what-it-is-and-how-to-use-it/) [Development

## Using PreparedStatement in JDBC With Examples

In this post, we'll see why and how to work with JDBC and PreparedStatement. We will also learn along with examples using code.

Read the post](https://coderpad.io/blog/development/using-preparedstatement-in-jdbc-with-examples/) [Development

## React vs React Native: The Differences Explained

React and React Native are similar in that they both allow web developers to build applications based on the unique concept of components. But the two frameworks do this in radically different ways. In this post, we cover the key differences between react and react native frameworks, implementation, and which is an appropriate choice.

Read the post](https://coderpad.io/blog/development/react-vs-react-native-the-differences-explained/) [Development

## Logging in Django

The most challenging part of fixing a problem is identifying it. Unfortunately, this statement is exceptionally true when it comes to debugging. Learn how to use the power of logging into your Django applications to improve your debug process in this article.

Read the post](https://coderpad.io/blog/development/logging-in-django/)

## Hire with confidence.

See why engineering teams trust CoderPad to evaluate developers with realistic, AI-ready hiring workflows.

[Request a demo](https://coderpad.io/sales/)