Difference between revisions of "MySQL Wildcard Filtering using LIKE"
m (Text replacement - "<google>ADSDAQBOX_FLOW</google>" to "<htmlet>adsdaqbox_flow</htmlet>") |
m (Text replacement - "<google>BUY_MYSQL</google>" to "<htmlet>mysql</htmlet>") |
||
| Line 8: | Line 8: | ||
| − | < | + | <htmlet>mysql</htmlet> |
Revision as of 22:13, 1 February 2016
| Previous | Table of Contents | Next |
| Advanced MySQL Data Filtering - AND, OR, NOT and IN | MySQL Regular Expression Searches |
| 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.
What are Wildcards?
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 |
