A2-02-09.DML-Using MySQL LIKE Operator To Select Data Based On Patterns
转载自：http://www.mysqltutorial.org/mysql-like/
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 the right 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 using the LIKE operator. See the following employees table.
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:
|
1
2
3
4
5
6
|
SELECT
employeeNumber, lastName, firstName
FROM
employees
WHERE
firstName LIKE 'a%';
|
MySQL scans the whole employees table to find an employee whose first name starts with character aand followed by any number of characters.
To search for employee whose last 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
employeeNumber, lastName, firstName
FROM
employees
WHERE
lastName 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 last names contain on string, you use the following query with pattern %on%
|
1
2
3
4
5
6
|
SELECT
employeeNumber, lastName, firstName
FROM
employees
WHERE
lastname LIKE '%on%';
|
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:
|
1
2
3
4
5
6
|
SELECT
employeeNumber, lastName, firstName
FROM
employees
WHERE
firstname LIKE 'T_m';
|
MySQL LIKE operator with NOT operator
The MySQL allows you 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 last name does not start with character B, you can use the NOT LIKE with the pattern as the following query:
|
1
2
3
4
5
6
|
SELECT
employeeNumber, lastName, firstName
FROM
employees
WHERE
lastName NOT LIKE 'B%';
|
Note 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 these cases, you can use the ESCAPE clause to specify the escape character so that MySQL interprets the wildcard character as a 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 use the pattern %\_20% as the following query:
|
1
2
3
4
5
6
|
SELECT
productCode, productName
FROM
products
WHERE
productCode LIKE '%\_20%';
|
Or you can specify a different escape character e.g., $ by using the ESCAPE clause:
|
1
2
3
4
5
6
|
SELECT
productCode, productName
FROM
products
WHERE
productCode LIKE '%$_20%' ESCAPE '$';
|
The pattern %$_20% matches any string that contains the _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 an 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 that has 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.
