MySQL LIKE Operator: 7 Examples and Best Practices

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.

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

• MySQL ORDER BY Clause: Syntax and 13 Examples
• GROUP BY Statement in MySQL With Twelve Examples
• MySQL JOINs Tutorial with Examples