Working with Strings - MySQL - page 164

Parentheses can be used to group alternations. For example, to match strings that consist
entirely of digits or entirely of letters, you might try this pattern, using an alternation:
mysql> SELECT '0m' REGEXP '^[[:digit:]]+|[[:alpha:]]+$';
+-------------------------------------------+
| '0m' REGEXP '^[[:digit:]]+|[[:alpha:]]+$' |
+-------------------------------------------+
| 1 |
+-------------------------------------------+
However, as the query result shows, the pattern doesn't work. That's because the ^ groups
with the first alternative, and the $ groups with the second alternative. So the pattern
actually matches strings that begin with one or more digits, or strings that end with one
or more letters. If you group the alternatives within parentheses, the ^ and $ apply to
both of them, and the pattern acts as you expect:
mysql> SELECT '0m' REGEXP '^([[:digit:]]+|[[:alpha:]]+)$';
+---------------------------------------------+
| '0m' REGEXP '^([[:digit:]]+|[[:alpha:]]+)$' |
+---------------------------------------------+
| 0 |
+---------------------------------------------+
Unlike SQL pattern matches, which are successful only if the pattern matches the entire
comparison value, regular expressions are successful if the pattern matches anywhere
within the value. The following two pattern matches are equivalent in the sense that
each one succeeds only for strings that contain a b character, but the first is more efficient
because the pattern is simpler:
'abc' REGEXP 'b'
'abc' REGEXP '^.*b.*$'
Regular expressions do not match NULL values. This is true both for REGEXP and for NOT
REGEXP :
mysql> SELECT NULL REGEXP '.*', NULL NOT REGEXP '.*';
+------------------+----------------------+
| NULL REGEXP '.*' | NULL NOT REGEXP '.*' |
+------------------+----------------------+
| NULL | NULL |
+------------------+----------------------+
Because a regular expression matches a string if the pattern is found anywhere in the
string, you must take care not to inadvertently specify a pattern that matches the empty
string. If you do, it matches any non- NULL value. For example, the pattern a* matches
any number of a characters, even none. If your goal is to match only strings containing
nonempty sequences of a characters, use a+ instead. The + requires one or more in‐
stances of the preceding pattern element for a match.
As with SQL pattern matches performed using LIKE , regular-expression matches per‐
formed with REGEXP sometimes are equivalent to substring comparisons. As shown in