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 operatorin 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:
SELECT employeeNumber, lastName, firstName FROM employees WHERE firstName LIKE 'a%'
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:
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 can execute following query:
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:
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 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:
SELECT employeeNumber, lastName, firstName FROM employees WHERE lastName NOT LIKE 'B%'
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:
SELECT productCode, productName FROM products WHERE productCode LIKE '%\_20%'
Or specify a different escape character e.g., ‘ $‘ by using the ESCAPE clause:
SELECT productCode, productName FROM products WHERE productCode LIKE '%$_20%' ESCAPE '$'
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 LIKEoperator 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.
随时随地看视频
