Oracle SQL - Select Regular Expressions


Oracle SQL supports four functions:


These SQL functions support regular expressions.

Regular Expression Operators and Metasymbols

The following table shows the most important regular expression metasymbols and their meanings.

Type Description
Postfix the operator follows its operand
Prefixthe operator precedes its operand
Infix the operator separates its operands
Nothing (empty) the operator has no operands

Common Regular Expression Operators and Metasymbols

Operator Type Description
*Postfix Zero or more occurrences
+Postfix One or more occurrences
?Postfix Zero or one occurrence
|InfixOperator to separate alternative choices
^Prefix Beginning of a string, or position immediately following a newline character
$Postfix End of the line
.-- Any single character
[[^]list]-- One character out of a list; a circumflex (^) at the beginning works as a negation; a dash (-) between two characters works as a range indicator
( ) -- Groups a (sub)expression, allowing you to refer to it further down in the expression
{m} Postfix Precisely m times
{m,} Postfix At least m times
{m,n}Postfix At least m times, and at most n times
\n -- Refers back to the nth subexpression between parentheses (n is a digit between 1 and 9)

If the square brackets notation does not give you enough precision or flexibility, use multicharacter collation elements, character classes, and equivalence classes, as follows:

  • Multicharacter collation elements are useful for certain languages. Valid values are predefined and depend on the NLS_SORT setting. Use [. and .] to enclose collation elements.
  • Character classes give you more power than the dash symbol. For example, you can refer to alphabetic characters, numeric digits, alphanumeric characters, blank spaces, punctuation, and so on. Use [: and :] to enclose character classes.
  • Equivalence classes can match all accented and unaccented versions of a letter. Use [= and =] to enclose equivalence classes.

Related Topics