Searching Using Regular Expressions - MariaDB Crash Course - page 66

If you were to try them both you'd discover that the first returns no data and the second
returns one row. Why is this?
As seen in Chapter 8, LIKE matches an entire column. If the text to be matched existed
in the middle of a column value, LIKE would not find it and the row would not be
returned (unless wildcard characters were used). REGEXP , on the other hand, looks for
matches within column values, and so if the text to be matched existed in the middle of
a column value, REGEXP would find it and the row would be returned. This is an impor-
tant distinction.
So can REGEXP be used to match entire column values (so that it functions like LIKE )?
Actually, yes, using the ^ and $ anchors, as explained later in this chapter.
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 expres-
sion 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.