REGEXP_REPLACE

The REGEXP_REPLACE function returns a modified version of the source string where occurrences of the regular expression pattern found in the source string are replaced with the specified replacement string.

source-string: An expression that specifies the string in which the search is to take place. The expression must return a value that is a built-in character string, graphic string, numeric, or datetime data type. If the value is not a UTF-16 DBCLOB, it is implicitly cast to a UTF-16 DBCLOB before searching for the regular expression pattern. A character string with the FOR BIT DATA attribute or a binary string is not supported. The length of a string must not be greater than 1 gigabyte.
pattern-expression: An expression that specifies the regular expression string that is the pattern for the search. The expression must return a value that is a built-in character string, graphic string, numeric, or datetime data type. If the value is not a UTF-16 DBCLOB, it is implicitly cast to a UTF-16 DBCLOB before searching for the regular expression pattern. A character string with the FOR BIT DATA attribute or a binary string is not supported. The length of the string must not be greater than 32K.
A valid pattern-expression consists of a set of characters and control characters that describe the pattern of the search. For a description of the valid control characters, see Regular expression control characters.

replacement-string: An expression that specifies the replacement string for matching substrings. The expression must return a value that is a built-in character string, graphic string, numeric, or datetime data type. If the value is not a UTF-16 DBCLOB, it is implicitly cast to a UTF-16 DBCLOB before searching for the regular expression pattern. A character string with the FOR BIT DATA attribute or a binary string is not supported. The length of the string must not be greater than 32K. If replacement-string is not specified, the default is the empty string.
The content of the replacement-string can include references to capture group text from the search to use in the replacement text. These references are of the form '$n' or '\n'33, where n is the number of the capture group and 0 represents the entire string that matches the pattern. The value for n must be in the range 0 to 9 and not greater than the number of capture groups in the pattern. For example, either '$2' or '\2' can be used to refer to the content found in source-string for the second capture group specified in pattern-expression. If the replacement-string must include a literal reference to a '$' or '\' character, then the '\' character must precede the literal reference so that it appears in the replacement-string as '\$' or '\\'.

start: An expression that specifies the position within source-string at which the search is to start. The expression must return a value of any built-in numeric, character-string, or graphic-string data type. The argument is cast to INTEGER before evaluating the function. For more information about converting to INTEGER, see INTEGER or INT. 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: An expression that specifies which occurrence of the pattern-expression within source-string to search for and replace. The expression must return a value of any built-in numeric, character-string, or graphic-string data type. The argument is cast to INTEGER before evaluating the function. For more information about converting to INTEGER, see INTEGER or INT. The value of the integer must be greater than or equal to 0. If occurrence is not specified, the default value is 0 which indicates that all occurrences of pattern-expression in source-string are replaced.

flags: An expression that specifies flags that control aspects of the pattern matching. The expression must return a value that is a built-in character string or graphic string data type. A character string with the FOR BIT DATA attribute or a binary string is not supported. The string can include one or more valid flag values and the combination of flag values must be valid. An empty string is the same as the value 'c'.
For a description of the valid flag characters, see Regular expression flag values.

The result of the function is a string. If there are no occurrences of the pattern to be replaced and no argument is null, the original string is returned. The data type of the string is the same as if the first and third arguments were concatenated except that the result is always a varying-length string. For more information, see With the concatenation operator.

The length attribute of the result data type is determined based on the length attributes of the source-string and the replacement-string using the following calculation: MIN(MaxTypeLen, LAS+(LAS+1)*LAR) where MaxTypeLen is the maximum length attribute for the data type of the result, LAS is the length attribute for the data type of source-string, and LAR is the length attribute for the data type of replacement-string. If replacement-string is not specified, the value for LAR is 0. If the actual length of the result string exceeds the maximum for the return data type, an error is returned.

The CCSID of the result is determined by the CCSID of the source-string and the replacement-string. The resulting CCSID is the same as if the first and third arguments were concatenated. For more information, see Rules for result data types.

If any argument of the REGEXP_REPLACE function can be null, the result can be null. If any argument is null, the result is the null value.

Notes

Prerequisites: In order to use the REGEXP_REPLACE function, the International Components for Unicode (ICU) option must be installed

Processing: The regular expression processing is done using the International Components for Unicode (ICU) regular expression interface. For more information see, http://userguide.icu-project.org/strings/regexp.

Example

Replace the second occurrence of the pattern 'R.d' with 'Orange' using a case sensitive search.
```
SELECT REGEXP_REPLACE(
  'Red Yellow RED Blue Red Green Blue',
  'R.d','Orange',1,2,'c') 
FROM sysibm.sysdummy1
```
The result is 'Red Yellow RED Blue Orange Green Blue'.