MySQL LIKE Operator: 7 Examples and Best Practices

We have been discussing various MySQL operators for quite some time now, including but not limited to the WHERE condition and SELECT statement. Now, the time to shine has come for the LIKE conditional operator used to search for patterns in strings with the help of wildcard characters. In this article, we will provide the basic syntax for using LIKE, along with illustrated examples of how to apply it in queries using one of the best MySQL IDEs on the market — dbForge Studio for MySQL.

MySQL LIKE Syntax

As mentioned before, the MySQL LIKE operator is used to look for specific patterns within a string in a table. In its most basic form, it will look somewhat like this:

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

• SELECT selects the columns to retrieve from the table.
• FROM specifies the table you are working with.
• WHERE specifies the search condition, which is the LIKE operator in this case.
• LIKE is followed by a pattern that specifies the string you are looking for. The pattern can include wildcards, which are special characters that can match any character or set of characters.

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 are done with the basics, let us get down to business and look into some close-to-real-life examples of the LIKE operator usage. We will explain how to use different wildcards individually and in combination, provide examples of the NOT LIKE syntax and case-insensitive search, and also cover how to use the UPDATE and DELETE commands with the LIKE operator.

MySQL LIKE: (%) percentage wildcard

In MySQL, the percentage symbol % is used as a wildcard character following the LIKE operator to match any string of zero or more characters.

Let us assume that there is a product table that contains product information, including its name. We want to find all products with paint in their name. We can use the % wildcard to match any characters before or after the word paint as follows:

SELECT * FROM product WHERE Name LIKE '%paint%';

As we can see, five matching records have been found:

The % wildcard can also be used to search for any number of characters in a particular position. For example, to find all products that start with the letter M, execute this query:

SELECT * FROM product WHERE name LIKE 'M%';

Consequently, the query results include all the products that start with M:

MySQL LIKE: (_) underscore wildcard

The next stop of our MySQL journey would be the (_) underscore wildcard. When it follows the LIKE operator, it substitutes one or more characters in a pattern you are looking for.

Let’s take the same product table as an example. Suppose all products in the table have a unique code consisting of two letters and four numbers separated by a dash. Each letter in the ProductNumber column denotes the category the item belongs to. For example, we need to find all the items under the C category, but we do not care about the rest of the characters. In this case, we can use the underscore (_) wildcard to represent those characters in the pattern:

SELECT * FROM product WHERE ProductNumber LIKE 'C_-____';

On executing the query, we see that the results include different kinds of categories, but all of them start with a C:

MySQL LIKE: combining (%) and (_) wildcards

The previous query example works and retrieves exactly what we intended to. However, it requires excessive symbols. To optimize it, let us combine the (%) and (_) wildcards:

SELECT * FROM product WHERE ProductNumber LIKE 'C_%';

Now we have the exact same output but with less ado in the query itself:

MySQL NOT LIKE Syntax

The MySQL NOT LIKE operator works in the opposite way to the LIKE comparison operator. Instead of retrieving data according to the specified pattern, it finds everything that does not match it. This operator can be useful when we want to exclude specific data from the search results. For example, if we are going to retrieve all products that do not belong to a certain category.

Earlier in this article, we worked with a product table where all products have a unique code consisting of two letters and four numbers with a dash in between. Suppose we need to retrieve all records that do not belong to a category starting with the letter C:

SELECT * FROM product WHERE ProductNumber NOT LIKE 'C%';

Unlike before, the query results now include all categories except for those that begin with the letter C.

MySQL LIKE: case-insensitive search

In certain situations, it may be necessary to retrieve data from a table regardless of the exact casing of the strings. This means that data should be retrieved irrespective of whether it is in uppercase or lowercase format.

In MySQL, the LIKE operator can perform a case-insensitive search with the help of the LOWER() or UPPER() functions. These functions essentially bring everything in a table to a common denominator. For example, we can convert all records to lowercase using LOWER() or uppercase using UPPER().

SELECT * FROM customer WHERE LOWER(last_name) LIKE '%jo%';

As a result, we do not have to worry about case sensitivity.

MySQL UPDATE with LIKE

It would be unfortunate and inconvenient if you could only use the LIKE operator with SELECT. However, MySQL offers us more options to play around with, such as pairing an UPDATE statement with LIKE. This combination updates multiple rows that match a specified pattern.

Let’s see an example of how the UPDATE and LIKE combination works in practice. Suppose we have a table named customer that contains all kinds of information about them, including their emails. We want to update all records in the table where the email address ends with example.com to use the domain newexample.com instead:

To achieve this goal, execute the UPDATE query:

UPDATE customer
SET email = REPLACE(email, 'example.com', 'newexample.com')
WHERE email LIKE '%example.com';

As a result, all the email endings will be updated with the domain specified in the query:

MySQL DELETE with LIKE

Besides SELECT and UPDATE, the LIKE operator can be used with the DELETE statement. It can come in handy when we wish to eliminate certain records that match the specified pattern.

Say, we are going to remove all the employees with the Trainee job title from the database.

Simply execute the DELETE query with the specified LIKE pattern:

DELETE FROM employee WHERE Job LIKE 'trainee%';

Now, there are no trainees among the staff anymore:

Conclusion

To sum up, we have discussed the usage of the MySQL LIKE operator for searching patterns in strings with the help of wildcard characters. The article provides the basic syntax for using LIKE along with illustrated examples of how to apply it in queries using the dbForge Studio for MySQL. This comprehensive MySQL GUI offers various features such as query creation and execution, development and debugging of stored routines, automated database object management, database comparison and synchronization, data analysis of tables, and many other functions. Give these advanced features a try – download a 30-day free trial.

Useful Links

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