Name

[ ] (Square Brackets) — Matches any of a set of characters

Synopsis

Use square brackets ([]) to create a matching list that will match on any one of the characters in the list.

The following example searches for a string of digits by applying the plus (+) quantifier to a matching list consisting of the set of digits 0-9:

SELECT REGEXP_SUBSTR(
   'Andrew is 14 years old.',
   '[0123456789]+ years old')
FROM dual;

14 years old

A better solution to this problem is to define a range of digits using the dash (-):

[0-9]+ years old

Even better is to specify a character class:

[[:digits:]]+ years old'

Begin a list with a caret (^) to create a non-matching list that specifies characters to which you do not want to match. The following extracts all of a sentence except the ending punctuation:

SELECT REGEXP_SUBSTR(
   'This is a sentence.',
   '.*[^.!:]')
FROM dual;

This is a sentence

Virtually all regular expression metacharacters lose their special meaning and are treated as regular characters when used within square brackets. The period in the previous SELECT statement provides an example of this, and Table 1-3 describes some exceptions to this general rule.

Table 1-3. Characters that retain special meaning within square brackets

Character

Meaning

^

An initial ^ defines a non-matching list. Otherwise, the ^ has no special meaning.

-

Specifies a range, for example 0-9. When used as the very first or very last character between brackets, or as the first character following a leading ^ within brackets, ...

Get Oracle Regular Expressions Pocket Reference now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.