Database Reference
In-Depth Information
For example, what if you wanted to find all products that started with a num-
ber (including numbers starting with a decimal point)? A simple search for
[0-9\\.]
(or
[[:digit:]\\.]
) would not work because it would find
matches anywhere within the text. The solution is to use the
^
anchor, as
seen here:
▼
Input
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;
▼
Output
+--------------+
| prod_name |
+--------------+
| .5 ton anvil |
| 1 ton anvil |
| 2 ton anvil |
+--------------+
▼
Analysis
^
matches the start of a string. As such,
^[0-9\\.]
matches
.
or any digit
only if they are the first characters within a string. Without the
^
, four other
rows would have been retrieved, too (those that have digits in the middle).
Note
The Dual Purpose
^
^
has two uses. Within a set (defined using
[
and
]
) it is used to
negate that set. Otherwise, it is used to refer to the start of a string.
Note
Making
REGEXP
Behave like
LIKE
Earlier in this chapter I mentioned that
LIKE
and
REGEXP
behaved differently in that
LIKE
matched an entire string and
REGEXP
matched
substrings, too. Using anchors,
REGEXP
can be made to behave just like
LIKE
by simply
starting each expression with
^
and ending it with
$
.
