Summary: MySQL provides LIKE operator in SQL standard. The MySQL LIKE operator is commonly used to select data based on patterns matching. Using MySQL LIKE in appropriate way is essential to increase application’s performance. In this tutorial, you will learn how to use MySQL LIKE and when to avoid using it to increase the speed of retrieving data from database table.
MySQL LIKE allows you to perform pattern matching in your characters column in a database table. MySQL LIKE is often used with SELECT statement in WHERE clause. MySQL provides you two wildcard characters for using with LIKE, the percentage % and underscore _.
- Percentage (%) wildcard allows you to match any string of zero or more characters
- Underscore (_) allows you to match any single character.
Let’s practice with couples of examples which use MySQL Like with different wildcard characters.
Suppose you want to search for employee in employees table who has first name starting with character ‘a’, you can do it as follows:
1 |
SELECT employeeNumber, lastName, firstName |
3 |
WHERE firstName LIKE 'a%' |
+----------------+----------+-----------+
| employeeNumber | lastName | firstName |
+----------------+----------+-----------+
| 1611 | Fixter | Andy |
+----------------+----------+-----------+
1 row in set (0.00 sec)
MySQL scans the whole employees table to find all employees which have first name starting with character ‘a’ and followed by any number of characters.
To search all employees which have last name ended with ‘on’ string you can perform the query as follows:
1 |
SELECT employeeNumber, lastName, firstName |
3 |
WHERE lastName LIKE '%on' |
+----------------+-----------+-----------+
| employeeNumber | lastName | firstName |
+----------------+-----------+-----------+
| 1088 | Patterson | William |
| 1216 | Patterson | Steve |
+----------------+-----------+-----------+
2 rows in set (0.00 sec)
If you know a searched string is embedded somewhere in a column, you can put the percentage wild card at the beginning and the end of it to find all possibilities. For example, if you want to find all employees which have last name containing ‘on’ string you can execute following query:
1 |
SELECT employeeNumber, lastName, firstName |
+----------------+-----------+-----------+
| employeeNumber | lastName | firstName |
+----------------+-----------+-----------+
| 1088 | Patterson | William |
| 1102 | Bondur | Gerard |
| 1216 | Patterson | Steve |
| 1337 | Bondur | Loui |
| 1504 | Jones | Barry |
+----------------+-----------+-----------+
5 rows in set (0.00 sec)
To search all employees whose name are such as Tom, Tim… You can use underscore wildcard
1 |
SELECT employeeNumber, lastName, firstName |
+----------------+----------+-----------+
| employeeNumber | lastName | firstName |
+----------------+----------+-----------+
| 1619 | King | Tom |
+----------------+----------+-----------+
1 row in set (0.00 sec)
The MySQL LIKE allows you to put the NOT keyword to find all strings which are unmatched with a specific pattern. Suppose you want to search for all employees whose last name are not starting with ‘B’, you can use the following query
1 |
SELECT employeeNumber, lastName, firstName |
3 |
WHERE lastName NOT LIKE 'B%' |
+----------------+-----------+-----------+
| employeeNumber | lastName | firstName |
+----------------+-----------+-----------+
| 1088 | Patterson | William |
| 1188 | Firrelli | Julie |
| 1216 | Patterson | Steve |
| 1286 | Tseng | Foon Yue |
| 1323 | Vanauf | George |
| 1370 | Hernandez | Gerard |
| 1401 | Castillo | Pamela |
| 1504 | Jones | Barry |
| 1611 | Fixter | Andy |
| 1612 | Marsh | Peter |
| 1619 | King | Tom |
| 1621 | Nishi | Mami |
| 1625 | Kato | Yoshimi |
| 1702 | Gerard | Martin |
+----------------+-----------+-----------+
14 rows in set (0.00 sec)
Be noted that SQL LIKE is not case sensitive so ‘b%’ and ‘B%’ are the same.
What if you want to search for records which have a field starting with a wildcard character? In this case, you can use ESCAPE to shows that the wildcard characters followed it has literal meaning not wildcard meaning. If ESCAPE does not specify explicitly, the escape character in MySQL by default is ‘\’. For example, if you want to find all products which as product code which has _20 embedded on it, you can perform following query
1 |
SELECT productCode, productName |
3 |
WHERE productCode LIKE '%\_20%' |
+-------------+-------------------------------------------+
| productCode | productName |
+-------------+-------------------------------------------+
| S10_2016 | 1996 Moto Guzzi 1100i |
| S24_2000 | 1960 BSA Gold Star DBD34 |
| S24_2011 | 18th century schooner |
| S24_2022 | 1938 Cadillac V-16 Presidential Limousine |
| S700_2047 | HMS Bounty |
+-------------+-------------------------------------------+
5 rows in set (0.00 sec)
MySQL LIKE gives you a convenient way to find records which have character columns match specified patterns. Because MySQL LIKE scans the whole table to find all the matching records therefore it does not allow database engine to use the index for fast searching. When the data in the table is big enough, the performance of MySQL LIKE will degrade. In some cases you can avoid this problem by using other techniques to achieve the same result as MySQL LIKE. For example, if you want to find all employees which have first name starting with a specified string you can use LEFT function in where clause like the following query:
2 |
SELECT employeeNumber, lastName, firstName |
4 |
WHERE LEFT(lastname,length(@str)) = @str; |
It returns the same result as the query below but it faster because we can leverage the index on the column lastname.
1 |
SELECT employeeNumber, lastName, firstName |
3 |
WHERE lastname LIKE 'b%' |
And another technique to achieve all string which end with a specified string by using RIGHT function. Suppose we want to retrieve all employees which have last name ended with ‘on’ string, we can use RIGHT function instead of MySQL LIKE as follows:
2 |
SELECT employeeNumber, lastName, firstName |
4 |
WHERE RIGHT (lastname,length(@str)) = @str; |
+----------------+-----------+-----------+
| employeeNumber | lastName | firstName |
+----------------+-----------+-----------+
| 1088 | Patterson | William |
| 1216 | Patterson | Steve |
+----------------+-----------+-----------+
2 rows in set (0.00 sec)
It returns the same result as the following query
1 |
SELECT employeeNumber, lastName, firstName |
3 |
WHERE lastname LIKE '%on' |
Related posts:
- Using MySQL SELECT Statement to Query Data
- How to Use MySQL Limit to Constrain Number of Returned Records
- How to Use MySQL Distinct to Eliminate Duplicate Rows
- Working with Database Table – Part I
- Managing Databases in MySQL
