MySQL LIKE

Summary: In this chapter, we will learn how to use MySQL LIKE operator to select data based on patterns.

Using 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 the right way is essential to increase the query performance.

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

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 using the LIKE operator with the employees table data in the sample database as shown in the picture below.

MySQL LIKE with percentage (%) wildcard

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

1 2 3 4 5 6

SELECT   employeeid, firstName, lastName FROM   employees WHERE   firstName LIKE 'a%';

MySQL scans the whole employees table to find an employee whose first name starts with character and followed by any number of characters.

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

1 2 3 4 5 6

SELECT   employeeid, firstName, lastName FROM   employees WHERE   firstName LIKE '%on';

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 first names contain “on” string, you use the following query with pattern %on%

1 2 3 4 5 6

SELECT   employeeid, firstName, lastName FROM   employees WHERE   firstName LIKE '%on%';

MySQL LIKE with underscore( _ ) wildcard

To find employee whose first name starts with ‘R’, ends with ‘m’ and contains any single character between e.g., Ram , Rom. we use the underscore wildcard to construct the pattern as follows:

1 2 3 4 5 6

SELECT  employeeid, firstName, lastName FROM  employees WHERE  firstname LIKE 'R_m';

MySQL NOT LIKE operator

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

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

1 2 3 4 5 6

SELECT   employeeid, firstName, lastName FROM   employees WHERE   firstname NOT LIKE 'A%';

Note that the pattern is not case sensitive with the LIKE operator, therefore, the a% and A%patterns produce the same result.

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

---

MySQL BETWEEN MySQL ORDER BY

---