Using MySQL LIKE Operator to Select Data Based On Patterns

Summary: in this tutorial, you will learn how to use MySQL LIKE operator to select data based on patterns.

The LIKE operator is commonly used to select data based on patterns. Using the LIKE operator in appropriate way is essential to increase the query performance.

The LIKE operator allows you to select data from a table based on a specified pattern. Therefore the LIKE operator is often used in the WHERE clause of the SELECT statement.

MySQL provides two wildcard characters for using with the LIKE operator, the percentage % and underscore _.

  • The percentage ( %) wildcard allows you to match any string of zero or more characters.
  • The underscore ( _) wildcard allows you to match any single character.

MySQL LIKE examples

Let’s practice with some examples of how to use the LIKE operator.

MySQL LIKE with percentage (%) wildcard

Suppose you want to search for employee whose first name starts with character ‘ a‘, you can use the percentage wildcard ( %) at the end of the pattern as follows:

mysql like example 1
MySQL scans the whole employees table to find employee whose first name starts with character ‘ a’ and followed by any number of characters.

To search for employee whose last name ends with ‘ on‘ string e.g., Patterson, Thompson, you can use the % wildcard at the beginning of the pattern as the following query:

mysql like example 2

If you know the searched string is embedded inside in the column, you can use the percentage ( %) wildcard at the beginning and the end of the pattern. For example, to find all employees whose last names contain ‘ on‘ string, you can execute following query:

mysql like example 3

MySQL LIKE with underscore( _) wildcard

To find employee whose first name starts with T, ends with m and contains any single character between e.g., Tom, Tim, you use the underscore wildcard to construct the pattern as follows:

mysql like example 4

MySQL LIKE operator with NOT operator

The MySQL allows you to combine the NOT operator with the LIKE operator to find string that does not match a specific pattern.

Suppose you want to search for employee whose last name does not start with character ‘ B‘, you can use the NOT LIKE with the pattern as the following query:

MySQL NOT LIKE example
Notice that the pattern is not case sensitive with the LIKE operator therefore the ‘b%’ and ‘B%’ patterns produce the same result.

MySQL LIKE with ESCAPE clause

Sometimes the pattern, which you want to match, contains wildcard character e.g., 10%, _20… etc. In this case, you can use the ESCAPE clause to specify the escape character so that MySQL interprets the wildcard character as literal character. If you don’t specify the escape character explicitly, the backslash character ‘ \‘ is the default escape character.

For example, if you want to find product whose product code contains string _20, you can perform following query:

Or specify a different escape character e.g., ‘ $‘ by using the ESCAPE clause:

mysql like example with escape character

The pattern %$_20% matches any string that contains _20 string.

The LIKE operator forces MySQL to scan the whole table to find the matching rows therefore it does not allow the database engine to use index for fast searching. As the result, the performance of the query that uses the LIKE operator degrades when you query data from a table with a large number of rows.

In this tutorial, you have learned how to use the LIKE operator to query data based on patterns, which is more flexible than using comparison operators.