Database Reference
In-Depth Information
Table 9.3
Repetition Metacharacters
Metacharacter
Description
*
0 or more matches
+
1 or more matches (equivalent to
{1,}
)
?
0 or 1 match (equivalent to
{0,1}
)
{n}
Specific number of matches
{n,}
No less than a specified number of matches
{n,m}
Range of matches (
m
not to exceed 255)
Following are some examples.
▼
Input
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name;
▼
Output
+----------------+
| prod_name |
+----------------+
| TNT (1 stick) |
| TNT (5 sticks) |
+----------------+
▼
Analysis
Regular expression
\\([0-9] sticks?\\)
requires some explanation.
\\
(
matches
(
,
[0-9]
matches any digit (
1
and
5
in this example),
sticks?
matches
stick
and
sticks
(the
?
after the
s
makes that
s
optional because
?
matches 0 or 1 occurrence of whatever it follows), and
\\)
matches the closing
)
. Without
?
it would have been difficult to match both
stick
and
sticks
.
Here's another example. This time we try to match four consecutive digits:
▼
Input
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;