MySQL Wildcard Filtering using LIKE

Previous	Table of Contents	Next
Advanced MySQL Data Filtering - AND, OR, NOT and IN		MySQL Regular Expression Searches

---

eBookFrenzy.com

Purchase and download the full PDF and ePub versions of this MySQL eBook for only $9.99

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 matches using more flexible criteria through the use of something called wildcards in conjunction with the SQL LIKE operator.

Contents

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 achieved using the underscore wildcard (_) together with the LIKE operator. To use this wildcard, simply place an underscore in the location 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)

Multiple Character Wildcards

Whilst the underscore wildcard is often useful, a far more powerful wildcard is the multiple character percent sign (%) wildcard. The percent sign wildcard can be used to represent any number of characters in a value match. When using wildcards, not only do the characters not have to match, but the number of characters also does not have to match.

Any number of wildcards can be placed in a string value. They can be placed at the start of the comparison value, for example to list all the rows where the product name ends in the words 'Computer Case':

mysql> SELECT * FROM product WHERE prod_name LIKE '% 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)

Alternatively, the wildcard could be placed at the end of a value, for example to retrieve all the items in stock manufactured by the fictitious WildTech Corporation:

mysql> SELECT * FROM product WHERE prod_name LIKE 'WildTech%';
+-----------+--------------------------+------------------------+
| prod_code | prod_name                | prod_desc              |
+-----------+--------------------------+------------------------+
|         3 | WildTech 250Gb 1700      | SATA Disk Drive        |
|        13 | WildTech Mouse (Optical) | Wireless Optical Mouse |
+-----------+--------------------------+------------------------+
2 rows in set (0.00 sec)

Multiple wildcards could be used to retrieve all the rows in our product table containing the word 'Mouse':

mysql> SELECT * FROM product WHERE prod_name LIKE '%Mouse%';
+-----------+--------------------------+------------------------+
| prod_code | prod_name                | prod_desc              |
+-----------+--------------------------+------------------------+
|         2 | EasyTech Mouse 7632      | Cordless Mouse         |
|        13 | WildTech Mouse (Optical) | Wireless Optical Mouse |
+-----------+--------------------------+------------------------+
2 rows in set (0.00 sec)

Finally, the wildcard could be placed in the center of a value, enabling us to locate a Microsoft branded keyboard without knowing the model number:

mysql> SELECT * FROM product WHERE prod_name LIKE 'Microsoft%Keyboard';
+-----------+--------------------------+-------------------+
| prod_code | prod_name                | prod_desc         |
+-----------+--------------------------+-------------------+
|         4 | Microsoft 10-20 Keyboard | Ergonomic Keyboard|
+-----------+--------------------------+-------------------+
1 row in set (0.00 sec)

Summary

In this chapter we have learned the power of wildcards when retrieving data from a database table. Widlcards make is easy to find matches without having to know the full string value needed to complete the retrieval. In addition, wildcards make it easy to find multiple matches without having to painstakingly list each possible match criteria.

in the next chapter we will look at an even more powerful and flexible pattern matching approach - regular expressions.

<google>BUY_MYSQ_BOTTOM</google>

---

Previous	Table of Contents	Next
Advanced MySQL Data Filtering - AND, OR, NOT and IN		MySQL Regular Expression Searches