Database Reference
In-Depth Information
▼
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.
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;
