MySQL Wildcard Filtering using LIKE
In previous chapters of MySQL Essentials we have looked in detail at retrieving data from MySQL database tables based on specific criteria. For example, we retrieved data based on specific string values. In this chapter we will look at retrieving data which matches using more flexible criteria through the use of something called wildcards in conjuction with the SQL LIKE operator.
What are Wildcards?
Wildcards are used when matching string values. A normal comparison requires the use of specific strings where each character in the two strings must match exactly before they are considered equal. Wildcards, on the other hand, provide more flexibility by allowing any character or group of characters in a string to be acceptable as a match for another string. As with most concepts, this is best demonstrated through examples.
Single Character Wildcards
Suppose we have developed an online commerce site that sells electronic items to customers throughout the world. One problem, even between English speaking nations is a difference in spelling. For example in the United States a particular color would be spelled as gray. In the United Kingdom the same color is spelled as grey. Supposing we had items in our product database that contained both spellings we would, under normal circumstances, need to perform a search for each spelling to retrieve the required data. For example, we might choose to use the IN clause:
mysql> SELECT * FROM product WHERE prod_name IN ('Grey Computer Case', 'Gray Computer Case');
+-----------+--------------------+-------------------+
| prod_code | prod_name | prod_desc |
+-----------+--------------------+-------------------+
| 11 | Grey Computer Case | ATX PC CASE |
| 12 | Gray Computer Case | ATX PC CASE (USA) |
+-----------+--------------------+-------------------+
2 rows in set (0.00 sec)
Another, easier option is to use a character wildcard in place of the 'e' and 'a' in the comparison value. This is achived using the underscore wildcard (_) together with the LIKE operator. To use this wildcard, simply place an underscore in the lcoation of the character for which multiple matches are acceptable. For example:
SELECT * FROM product WHERE prod_name LIKE 'Gr_y Computer Case';
In the above SELECT statement, any character between the 'Gr' and 'y' will be considered a match, resulting in the following output:
+-----------+--------------------+-------------------+ | prod_code | prod_name | prod_desc | +-----------+--------------------+-------------------+ | 11 | Grey Computer Case | ATX PC CASE | | 12 | Gray Computer Case | ATX PC CASE (USA) | +-----------+--------------------+-------------------+ 2 rows in set (0.00 sec)
