Oracle Regular Expressions Pocket Reference

Name

* (Asterisk) — Matches zero or more

Synopsis

The asterisk (*) is a quantifier that applies to the preceding regular expression element. It specifies that the preceding element may occur zero or more times.

The following example uses ^.*$ to return the second line of a text value.

SELECT REGEXP_SUBSTR('Do not' || CHR(10) 
                  || 'Brighten the corner!'
                    ,'^.*$',1,2,'m')
FROM dual;

Brighten the corner!

The 'm' match_parameter is used to cause the ^ and $ characters to match the beginning and end of each line, respectively. The .* matches any and all characters between the beginning and end of the line. The first match of this expression is the string “Do not”. We passed a 2 as the fourth parameter to request the second occurrence of the regular expression.

If the previous element is a bracket expression, the asterisk matches a string of zero or more characters from the set defined by that expression:

SELECT REGEXP_SUBSTR('123789',
                     '[[:digit:]]*')
FROM dual;

123789

Likewise, the preceding element might be a subexpression. In the following example, each fruit name may be followed by zero or more spaces, and we are looking for any number of such fruit names:

SELECT REGEXP_SUBSTR('apple apple orange wheat',
       '((apple|orange|pear)[[:space:]]*)*')
FROM dual;

apple apple orange

Watch out! The asterisk can surprise you. Consider the following:

SELECT REGEXP_SUBSTR('abc123789def',
                     '[[:digit:]]*')
FROM dual;

The result of executing this query will be a NULL. Why? Because [[:digit:]] is optional. When ...