Need to filter search results based on partial matches in MySQL?
The MySQL LIKE operator is your go-to tool for this task. This tool allows you to find records in your database even when you don’t know the exact value. To achieve this, the operator lets you match string patterns using special wildcard characters. For example, you can quickly locate all customers whose names start with “Jo” or all email addresses containing a specific domain. Since pattern matching is a frequent requirement in real-world database operations, such as search features, filtering large datasets, or validating input, mastering the LIKE operator is an essential skill.
In this guide, we’ll walk you through the syntax of the LIKE operator, explain the wildcard symbols it uses, and provide clear, practical examples that you can try out right away. We’ll also highlight best practices to ensure your queries remain efficient and accurate.
All examples in this guide will be demonstrated in dbForge Studio for MySQL, one of the best MySQL IDEs available for working with MySQL.
MySQL LIKE syntax
To search for patterns in text columns using SQL, you can use the following LIKE operator syntax to complete this action:
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
The SELECT statement defines which columns to retrieve, FROM specifies the table, and the WHERE clause applies the condition, but with a pattern defined by LIKE. Patterns in MySQL can include wildcards that make partial matches possible. Here is how this works:
- The percent sign (%) matches zero or more characters. For example,
LIKE %text%
will return any value containing the word “text” anywhere inside. - The underscore (_) matches a single character. For instance,
LIKE _ext%
will match “text,” “next,” or any word where the second through fourth letters are “ext.” - Another common example is
LIKE A%
, which finds all values starting with the letter “A.”
This makes LIKE especially useful for filtering names, searching email addresses, or building search functionality in applications.
Table of contents- Query examples of using LIKE in MySQL
- MySQL LIKE: (%) percentage wildcard
- MySQL LIKE: (_) underscore wildcard
- MySQL LIKE: combining (%) and (_) wildcards
- MySQL NOT LIKE syntax
- MySQL LIKE: case-insensitive search
- MySQL UPDATE with LIKE
- MySQL DELETE with LIKE
- Conclusion
Query examples of using LIKE in MySQL
Now that we’ve covered the syntax, let’s explore how the LIKE
operator works in real-life SQL queries.
The LIKE
operator in SQL can be used in many different ways to filter and manipulate data, depending on the search pattern you need. For instance, you can apply wildcards like % and _ to search for partial matches, whether it’s names starting with a certain letter, emails containing a specific domain, or product codes with a fixed pattern. Beyond simple filtering, LIKE
also supports variations such as NOT LIKE
for excluding certain results and case-insensitive searches for flexible matching.
Additionally, the operator isn’t limited to SELECT
queries. You can combine LIKE
with UPDATE
or DELETE
statements to modify or remove rows that match specific patterns.
In the sections below, we’ll walk through clear, practical examples that demonstrate these different use cases.
MySQL LIKE: (%) percentage wildcard
To match any substring in a column, use the %
wildcard in a LIKE
clause. The %
symbol represents zero or more characters, making it ideal for flexible searches in text-based columns.
Example 1: match substrings
Let’s say you have a product table containing product information, including names. If you want to find all products with the word paint anywhere in their name, you can use the %
wildcard before and after the keyword:
SELECT *
FROM product
WHERE Name LIKE '%paint%';
This query returns every product that contains the substring “paint” regardless of what characters appear before or after it.
Example 2: Match starting characters
The %
wildcard can also be applied to find all values starting with a specific character or word. For example, to return all products that begin with the letter “M”:
SELECT *
FROM product
WHERE Name LIKE 'M%';
This query matches names such as Metal, Mountain, and ML.
Pro tip: Learning operators in MySQL and mariaDB becomes much easier when you can visualize the output. Try dbForge Studio for MySQL to better understand how LIKE operator works.
MySQL LIKE: (_) underscore wildcard
To match exactly one character in a string, use the _ wildcard with the LIKE operator. Unlike the % symbol, which can represent any number of characters, the underscore stands for a single character only. This makes it useful when you know the overall structure of a value but need flexibility for specific positions.
Let’s continue with the product table example.
Suppose the ProductNumber
column stores codes in the format of two letters, a dash, and four digits (e.g., AB-1234). Each letter represents a category. If you want to find all items in the C category, regardless of the following characters, you can use the underscore wildcard:
SELECT *
FROM product
WHERE ProductNumber LIKE 'C_-____';
This query matches every product code that starts with C, followed by one letter, a dash, and four digits.
MySQL LIKE: combining (%) and (_) wildcards
You can simplify LIKE
queries by combining %
and _ wildcards when appropriate. While the underscore (_) is useful for matching a single character in a specific position, sometimes a query can be made shorter and easier to read by replacing multiple underscores with the percent (%) wildcard.
For example, suppose you want to return all products in the ProductNumber
column that begin with the letter C. Instead of writing several underscores to represent each character position, you can combine the two wildcards as follows:
SELECT *
FROM product
WHERE ProductNumber LIKE 'C_%';
This query delivers the same results as the longer version but with a more concise and maintainable pattern. It finds every product code starting with C followed by any number of characters.
MySQL NOT LIKE syntax
The NOT LIKE operator filters out values that match a specific pattern. Instead of returning rows that follow the given condition, it retrieves everything that does not match. This makes it especially useful when you want to exclude certain categories, keywords, or identifiers from your results.
For example, consider the product table where each item has a unique ProductNumber consisting of two letters, a dash, and four digits (e.g., AB-1234). If you need to return all products that do not belong to the C category, you can use NOT LIKE as follows:
SELECT *
FROM product
WHERE ProductNumber NOT LIKE 'C%';
This query returns every product except those whose ProductNumber
starts with the letter C. In other words, it filters out category C and keeps all other categories.
MySQL LIKE: case-insensitive search
To perform a case-insensitive search using LIKE
, wrap the column in the LOWER()
or UPPER()
function. This approach ensures that all values are compared in the same letter case, so the query returns matches regardless of whether the text is stored in uppercase, lowercase, or mixed case.
For example, assuming you want to find all customers whose last name contains “jo.” By converting every value in the last_name column to lowercase, you can safely search without worrying about case sensitivity:
SELECT *
FROM customer
WHERE LOWER(last_name) LIKE '%jo%';
This query matches names such as Johnson, JERRY, or jones, treating them all as equivalent. Using LOWER()
or UPPER()
in combination with LIKE
is a reliable way to make your searches flexible across different text formats.
MySQL UPDATE with LIKE
Need to update rows based on a pattern? Here’s how to use LIKE with UPDATE in MySQL. This combination allows you to modify multiple rows at once if they match a specified string pattern, making bulk updates efficient and precise.
For example, imagine you have a customer table that stores email addresses and you need to replace every occurrence of the domain example.com with newexample.com. You can pair the UPDATE statement with LIKE to target only those rows:
UPDATE customer
SET email = REPLACE(email, 'example.com', 'newexample.com')
WHERE email LIKE '%example.com';
This query searches for all email addresses ending with example.com and updates them to the new domain. As a result, every affected record is updated in a single step without the need to edit rows individually.
MySQL DELETE with LIKE
To delete records that match a specific pattern, use the LIKE
operator with the DELETE
statement. This approach is useful when you need to remove multiple rows at once based on text patterns rather than exact values.
For example, suppose you have an employee table and want to remove all staff members whose job titles begin with Trainee. Instead of deleting them one by one, you can use the following query:
DELETE FROM employee
WHERE Job LIKE 'trainee%';
This query would first find every job title starting with “trainee” and then deletes those rows from the table. After execution, all trainee records are removed, leaving only the permanent staff.
Conclusion
Mastering the LIKE operator allows you to write more efficient, flexible SQL queries. In this guide, we explored the LIKE operator syntax, the use of %
and _ wildcards, case-insensitive searches, and practical applications with SELECT
, UPDATE
, and DELETE
statements. With these skills, you can filter, modify, or clean up data patterns in MySQL with precision and confidence.
To make working with MySQL even more productive, consider using dbForge Studio for MySQL—a powerful IDE designed to simplify and enhance database management. With it, you can:
- Write and execute queries with ease.
- Develop and debug stored routines.
- Automate object management tasks.
- Compare and synchronize databases.
- Analyze and manage table data visually.
Try these advanced features yourself with a 30-day free trial of dbForge Studio for MySQL.
Useful links