REGEX_REPLACE

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > Basic Building Blocks > Functions > Built-in Functions >

REGEX_REPLACE

Previous pageReturn to chapter overviewNext page

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', utf16Col, 'HAHA', 1, 0) from t5053;