MariaDB Crash Course - Ben Forta [23]
8. Using Wildcard Filtering
In this chapter, you learn what wildcards are, how they are used, and how to perform wildcard searches using the LIKE operator for sophisticated filtering of retrieved data.
Using the LIKE Operator
All the previous operators we studied filter against known values. Be it matching one or more values, testing for greater-than or less-than known values, or checking a range of values, the common denominator is that the values used in the filtering are known. But filtering data that way does not always work. For example, how could you search for all products that contained the text anvil within the product name? That cannot be done with simple comparison operators; that’s a job for wildcard searching. Using wildcards, you can create search patterns that can be compared against your data. In this example, if you want to find all products that contain the word anvil, you could construct a wildcard search pattern enabling you to find that anvil text anywhere within a product name.
* * *
New Term: Wildcards
Special characters used to match parts of a value.
* * *
* * *
New Term: Search pattern
A search condition made up of literal text, wildcard characters, or any combination of the two.
* * *
The wildcards themselves are actually characters that have special meanings within SQL WHERE clauses, and SQL supports several wildcard types.
To use wildcards in search clauses, the LIKE operator must be used. LIKE instructs MariaDB that the following search pattern is to be compared using a wildcard match rather than a straight equality match.
* * *
Note: Predicates
When is an operator not an operator? When it is a predicate. Technically, LIKE is a predicate, not an operator. The end result is the same; just be aware of this term in case you run across it in the MariaDB documentation.
* * *
The Percent Sign (%) Wildcard
The most frequently used wildcard is the percent sign (%). Within a search string, % means match any number of occurrences of any character. For example, to find all products that start with the word jet, you can issue the following SELECT statement:
Input
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';
Output
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| JP1000 | JetPack 1000 |
| JP2000 | JetPack 2000 |
+---------+--------------+
Analysis
This example uses a search pattern of 'jet%'. When this clause is evaluated, any value that starts with jet is retrieved. The % tells MariaDB to accept any characters after the word jet, regardless of how many characters there are.
* * *
Note: Case-Sensitivity
Depending on how the column is defined in MariaDB, searches might be case-sensitive, in which case 'jet%' would not match JetPack 1000.
* * *
Wildcards can be used anywhere within the search pattern, and multiple wildcards can be used as well. The following example uses two wildcards, one at either end of the pattern:
Input
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%anvil%';
Output
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| ANV01 | .5 ton anvil |
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+--------------+
Analysis
The search pattern '%anvil%' means match any value that contains the text anvil anywhere within it, regardless of any characters before or after that text.
Wildcards can also be used in the middle of a search pattern, although that is rarely useful. The following example finds all products that begin with an s and end with an e:
Input
SELECT prod_name
FROM products
WHERE prod_name LIKE 's%e';
* * *
Tip: Searching For Partial Email Addresses
There is one situation in which wildcards may indeed be useful in the middle of a search pattern, and that is looking for email addresses based on a partial address, such as WHERE email LIKE 'b%@forta.com'.
* * *
It is important to note that, in addition