Oracle SQL RegEx Cheat Sheet

Functions

Function	Description
REGEXP_LIKE (source, pattern [, match_parameter ])	Returns TRUE if the source matches the regular expression pattern.
REGEXP_INSTR (source, pattern [, start [, match_occurrence [, return_option [, match_parameter ]]]])	Returns the position of the matched substring.
REGEXP_SUBSTR (source, pattern [, start [, match_occurrence [, match_parameter ]]])	Returns the matched substring.
REGEXP_REPLACE (source, pattern, replace_string [, start [, match_occurrence [, match_parameter ]]])	Replaces occurrences of the pattern with the replace_string.
REGEXP_COUNT (source, pattern [, start [, match_parameter ]])	Returns the number of times the pattern occurs in the source.

Match Parameters

Parameter	Description
c	Case-sensitive matching.
i	Case-insensitive matching.
n	Allows the period (.) to match the newline character.
m	Treats the source string as multiple lines.
x	Ignores whitespace characters.

Common Patterns

Pattern	Description
^	Matches the beginning of a string.
$	Matches the end of a string.
.	Matches any single character.
[...]	Matches any character listed between the square brackets.
[^...]	Matches any character not listed between the square brackets.
*	Matches zero or more occurrences of the preceding element.
+	Matches one or more occurrences of the preceding element.
?	Matches zero or one occurrence of the preceding element.

POSIX Character Classes

Pattern	Description
[:alpha:]	Matches any alphabetic character.
[:digit:]	Matches any digit.
[:alnum:]	Matches any alphanumeric character.
[:lower:]	Matches any lowercase letter.
[:upper:]	Matches any uppercase letter.
[:space:]	Matches any whitespace character.
[:punct:]	Matches any punctuation character.

Quantifiers

Pattern	Description
{n}	Matches exactly n occurrences of the preceding character or group.
{n,}	Matches n or more occurrences of the preceding character or group.
{n,m}	Matches between n and m occurrences of the preceding character or group.

Escape Sequences

Pattern	Description
\t	Matches a tab character.
\n	Matches a newline character.
\r	Matches a carriage return character.
\	Matches a backslash.
\^	Matches a caret (^) character.
\$	Matches a dollar ($) character.

Groups and Backreferences

Pattern	Description
(...)	Defines a capturing group.
\1	Backreference to the first capturing group.
\2	Backreference to the second capturing group, and so on.

Character Class Shortcuts

Pattern	Description
\d	Matches any digit, equivalent to [0-9].
\D	Matches any non-digit.
\w	Matches any word character (alphanumeric plus underscore).
\W	Matches any non-word character.
\s	Matches any whitespace character.
\S	Matches any non-whitespace character.

Boundary Matchers

Pattern	Description
\b	Matches a word boundary.
\B	Matches a non-word boundary.
\A	Matches the beginning of the input.
\Z	Matches the end of the input, excluding the final terminator, if any.
\z	Matches the absolute end of the input.

Unicode Support

Pattern	Description
\X	Matches a Unicode extended grapheme cluster.

Zero-width Assertions

Pattern	Description
(?=...)	Positive lookahead assertion.
(?!...)	Negative lookahead assertion.
(?<=...)	Positive lookbehind assertion.
(?	Negative lookbehind assertion.

Modifiers

Modifier	Description
x	Extended mode. Whitespace within the pattern is ignored, except when in a character class or preceded by an unescaped backslash.