MariaDB Crash Course - Ben Forta [26]
* * *
* * *
Tip: Matches Are Not Case-Sensitive
Regular expression matching in MariaDB is not case-sensitive (either case will be matched). To force case-sensitivity, you can use the BINARY keyword, as in WHERE prod_name REGEXP BINARY 'JetPack .000'
* * *
Performing OR Matches
To search for one of two strings (either one or the other), use | as seen here:
Input
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;
Output
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
Analysis
Here the regular expression 1000|2000 was used. | is the regular expression OR operator. It means match one or the other, and so both 1000 and 2000 matched and were returned.
Using | is functionally similar to using OR statements in SELECT statements, with multiple OR conditions being consolidated into a single regular expression.
* * *
Tip: More than Two OR Conditions
More than two OR conditions may be specified. For example, '1000|2000|3000' would match 1000 or 2000 or 3000.
* * *
Matching One of Several Characters
. matches any single character. But what if you wanted to match only specific characters? You can do this by specifying a set of characters enclosed within [ and ], as seen here:
Input
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton'
ORDER BY prod_name;
Output
+-------------+
| prod_name |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
Analysis
Here the regular expression [123] Ton was used. [123] defines a set of characters, and here it means match 1 or 2 or 3, so both 1 ton and 2 ton matched and were returned (there was no 3 ton).
As you have just seen, [] is another form of OR statement. In fact, the regular expression [123] Ton is shorthand for [1|2|3] Ton, which also would have worked. But the [] characters are needed to define what the OR statement is looking for. To better understand this, look at the next example:
Input
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1|2|3 Ton'
ORDER BY prod_name;
Output
+---------------+
| prod_name |
+---------------+
| 1 ton anvil |
| 2 ton anvil |
| JetPack 1000 |
| JetPack 2000 |
| TNT (1 stick) |
+---------------+
Analysis
Well, that did not work. The two required rows were retrieved, but so were three others. This happened because MariaDB assumed that you meant ‘1’ or ‘2’ or ‘3 ton’. The | character applies to the entire string unless it is enclosed with a set.
Sets of characters can also be negated. That is, they’ll match anything but the specified characters. To negate a character set, place a ^ at the start of the set. So, whereas [123] matches characters 1, 2, or 3, [^123] matches anything but those characters.
Matching Ranges
Sets can be used to define one or more characters to be matched. For example, the following matches digits 0 through 9:
[0123456789]
To simplify this type of set, - can be used to define a range. The following is functionally identical to the list of digits just seen:
[0-9]
Ranges are not limited to complete sets—[1-3] and [6-9] are valid ranges, too. In addition, ranges need not be numeric, and so [a-z] matches any alphabetical character.
Here is an example:
Input
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;
Output
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
Analysis
Here the regular expression [1-5] Ton was used. [1-5] defines a range, and so this expression means match 1 through 5, and so three matches were returned. .5 ton was returned because 5 ton matched (without the . character).
Matching Special Characters
The regular expression language is made up of special characters that have specific meanings. You’ve already seen ., [], |, and -, and there are others, too. Which begs the question, if you needed to