MariaDB Crash Course - Ben Forta [24]
* * *
Note: Watch for Trailing Spaces
Trailing spaces can interfere with wildcard matching. For example, if any of the anvils had been saved with one or more spaces after the word anvil, the clause WHERE prod_name LIKE '%anvil' would not have matched them as there would have been additional characters after the final l. One simple solution to this problem is to always append a final % to the search pattern. A better solution is to trim the spaces using functions, as discussed in Chapter 11, “Using Data Manipulation Functions.”
* * *
* * *
Caution: Watch for NULL
While it may seem that the % wildcard matches anything, there is one exception, NULL. Not even the clause WHERE prod_name LIKE '%' will match a row with the value NULL as the product name.
* * *
The Underscore (_) Wildcard
Another useful wildcard is the underscore (_). The underscore is used just like %, but instead of matching multiple characters, the underscore matches just a single character.
Take a look at this example:
Input
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';
Output
+---------+-------------+
| prod_id | prod_name |
+---------+-------------+
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+-------------+
Analysis
The search pattern used in this WHERE clause specifies a wildcard followed by literal text. The results shown are the only rows that match the search pattern: The underscore matches 1 in the first row and 2 in the second row. The .5 ton anvil product did not match because the search pattern matched a single character, not two. By contrast, the following SELECT statement uses the % wildcard and returns three matching products:
Input
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '% ton anvil';
Output
+---------+--------------+
| prod_id | prod_name |
+---------+--------------+
| ANV01 | .5 ton anvil |
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+--------------+
Unlike %, which can match zero characters, _ always matches one character—no more and no less.
Tips for Using Wildcards
As you can see, MariaDB’s wildcards are powerful. But that power comes with a price: Wildcard searches typically take far longer to process than any other search types discussed previously. Here are some tips to keep in mind when using wildcards:
• Don’t overuse wildcards. If another search operator will do, use it instead.
• When you do use wildcards, try to not use them at the beginning of the search pattern unless absolutely necessary. Search patterns that begin with wildcards are the slowest to process.
• Pay careful attention to the placement of the wildcard symbols. If they are misplaced, you might not return the data you intended.
Having said that, wildcards are an important and useful search tool and one that you will use frequently.
Summary
In this chapter, you learned what wildcards are and how to use SQL wildcards within your WHERE clauses. You also learned that wildcards should be used carefully and never overused.
9. Searching Using Regular Expressions
In this chapter, you learn how to use regular expressions within MariaDB WHERE clauses for greater control over data filtering.
Understanding Regular Expressions
The filtering examples in the previous two chapters enabled you to locate data using matches, comparisons, and wildcard operators. For basic filtering (and even some not-so-basic filtering) this might be enough. But as the complexity of filtering conditions grows, so does the complexity of the WHERE clauses themselves.
And this is where regular expressions become useful. Regular expressions are part of a special language used to match text. If you needed to extract phone numbers from a text file, you might use a regular expression. If you needed to locate all files with digits in the middle of their names, you might use a regular expression.