(tomado de http://www.mysqltutorial.org/sql-like-mysql.aspx )
SQL LIKE allows you to perform pattern matching in your characters column in a database table. SQL LIKE is often used with SELECT statement in WHERE clause. MySQL provides you two wildcard characters for use 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 sing character.
Let’s practice with couples of examples which use SQL Like with different wildcard characters.
Suppose you want to search all employees in employees table who have first namestarting with character ‘a’, you can do it as follows:
SELECT employeeNumber, lastName, firstName
FROM employees
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 namestarting 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:
SELECT employeeNumber, lastName, firstName
FROM employees
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’ stringyou can execute following query:
SELECT employeeNumber, lastName, firstName
FROM employees
WHERE lastName LIKE '%on%'
+----------------+-----------+-----------+
| 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
SELECT employeeNumber, lastName, firstName
FROM employees
WHERE firstName LIKE 'T_m'
+----------------+----------+-----------+
| employeeNumber | lastName | firstName |
+----------------+----------+-----------+
| 1619 | King | Tom |
+----------------+----------+-----------+
1 row in set (0.00 sec)
SQL LIKE allows you to put the NOT keyword to find all strings which are unmatched with a specific pattern. Suppose you want to search all employees whose last name are not starting with ‘B’ you can perform the following query
SELECT employeeNumber, lastName, firstName
FROM employees
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 wildcardcharacter? 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
SELECT productCode, productName
FROM products
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)
SQL LIKE gives you a convenient way to find records which have character columns match specified patterns. Because SQL LIKE scans the whole table to find all the matching records therefore it does not allow database engine to use the index when searching. When the data in the table is big enough, the performance of SQL LIKE will degrade. In some cases you can avoid this problem by using other techniques to achieve the same result as SQL LIKE. For example, if you want to find all employeeswhich have first name starting with a specified string you can use LEFT function in where clause like the following query
SET @str = 'b';
SELECT employeeNumber, lastName, firstName
FROM employees
WHERE LEFT(lastname,length(@str)) = @str;
It returns the same result as the query bellow but it faster because we can leverage the index on the column lastname.
SELECT employeeNumber, lastName, firstName
FROM employees
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 nameended with ‘on’ string, we can use RIGHT function instead of SQL LIKE as bellow:
SET @str = 'on';
SELECT employeeNumber, lastName, firstName
FROM employees
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
SELECT employeeNumber, lastName, firstName
FROM employees
WHERE lastname LIKE '%on'
