REGEX_REPLACE

Replaces all substrings matching a regular expression in a defined source called sourceString. Returns a string having the same character encoding as sourceString. If there is a match, the source string is returned with the replacement string substituted for the matching substring.

Syntax

REGEX_REPLACE
(
sourceString VARCHAR(65535),
javaRegexPattern VARCHAR(65535),
replacementString VARCHAR(65535),
startPosition int,
occurrence int
);

Details

Raises an error if javaRegexPattern or replacementString does not have the same character encoding as sourceString.

Raises an error if startPosition < 1 or if occurrence < 0

Returns NULL if any of the arguments is NULL.

Returns sourceString if startPosition is greater than the length of sourceString.

If occurrence = 0, returns sourceString with ALL substrings matching javaRegexPattern (from startPosition onward) replaced by replacementString.

Parameters

sourceString

Specifies the string in which the search is to take place. Expression can evaluate to VARCHAR or CHAR, with max length of 65535. No other restrictions.

javaRegexPattern

Specifies the regular expression string to be used as search pattern, as defined in java.util.regex.pattern. The length of the pattern cannot exceed 65535 characters. Must have the same character encoding as sourceString.

replacementString

Specifies the replacement string for matching substrings. Expression can evaluate to VARCHAR or CHAR, with max length of 65535. No other restrictions.

startPosition

A positive integer which specifies the position within source-string at which the search is to start. The value of the integer must be greater than or equal to 1. If the value of the integer is greater than the actual length of the source-string, the original string is returned.

occurrence

A positive integer and specifies which occurrence of javaRegexPattern (from startPosition onward) should be replaced by replacementString. In other words, occurrence specifies the nth occurrence of javaRegexPattern within sourceString to search for and replace. The value of occurrence must be greater than or equal to 0. If occurrence is 0, REGEX_REPLACE replaces all occurrences of javaRegexPattern in sourceString.

Example

values regex_replace('The pen is mightier than the sword', 'i[a-zA-Z]', 'HAHA', 1, 0);
select regex_replace('The pen is mightier than the sword', myColumn, 'HAHA', 1, 0) from t5053;

Quick Regex Reference

For full details on Regex, see java.util.regex.pattern

Pattern Description
[xyz] Find single character of: x, y or z
[^abc] Find any single character except: x, y, or z
[r-z] Find any single character between r-z
[r-zR-Z] Find any single character between r-z or R-Z
^ Start of line
$ End of line
\A Start of string
\z End of string
. Any single character
\s Find any whitespace character
\S Find any non-whitespace character
\d Find any digit
\D Find any non-digit
\w Find any word character (letter, number, underscore)
\W Find any non-word character
\b Find any word boundary
(...) Capture everything enclosed
(x|y) Find x or y (also works with symbols such as \d or \s)
x? Find zero or one of x (also works with symbols such as \d or \s)
x* Find zero or more of x (also works with symbols such as \d or \s)
x+ Find one or more of x (also works with symbols such as \d or \s)
x{3} Find exactly 3 of x (also works with symbols such as \d or \s)
x{3,} Find 3 or more of x (also works with symbols such as \d or \s)
x{3,6} Find between 3 and 6 of x (also works with symbols such as \d or \s)