34,333
edits
Changes
→Single Character Wildcards
2 rows in set (0.00 sec)
</pre>
== 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':
<pre>
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)
</pre>
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:
<pre>
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)
</pre>
Multiple wildcards could be used to retrieve all the rows in our product table containing the word 'Mouse':
<pre>
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)
</pre>
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:
<pre>
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)
</pre>
== Summary ==
In this chapter we have learned the power of widlcards 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 muliple 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 - [[Reqular Expressions in MySQL|regular expressions]].
