Searching Using Regular Expressions - MariaDB Crash Course - page 65

▼ Analysis
This statement looks much like the ones that used LIKE (in Chapter 8, “Using
Wildcard Filtering”), except that the keyword LIKE has been replaced with
REGEXP . This tells MariaDB that what follows is to be treated as a regular
expression (one that just matches the literal text 1000 ).
So, why bother using a regular expression? Well, in the example just used,
regular expressions really add no value (and probably hurt performance), but
consider this next example:
▼ Input
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
▼ Output
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
▼ Analysis
Here the regular expression .000 was used. . is a special character in the regu-
lar expression language. It means match any single character , and so both 1000
and 2000 matched and were returned.
Of course, this particular example could also have been accomplished using
LIKE and wildcards (as seen in Chapter 8).
Note
LIKE Versus REGEXP There is one important difference between LIKE and REGEXP .
Look at these two statements:
SELECT prod_name
FROM products
WHERE prod_name LIKE '1000'
ORDER BY prod_name;
and
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;