DB2 Version 9.7 for Linux, UNIX, and Windows

replace function

The fn:replace function compares each set of characters within a string to a specific pattern, and then it replaces the characters that match the pattern with another set of characters.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-fn:replace(source-string,pattern,replacement-string-+--------+-)-><
                                                       '-,flags-'     

source-string
A string that contains characters that are to be replaced.

source-string is an xs:string value or the empty sequence.

pattern
A regular expression that is compared to source-string. A regular expression is a set of characters, wildcards, and operators that define a string or group of strings in a search pattern.

pattern is an xs:string value.

replacement-string
A string that contains characters that replace characters that match pattern in source-string.

replacement-string is an xs:string value.

replacement-string can contain the variables $0 to $9. $0 represents the entire string in pattern. The variable $1 through $9 represent one of nine possible parenthesized subexpressions in pattern. ($1 represents the first subexpression, $2 represents the second subexpression, and so on.)

To use the literal dollar sign ($) in replacement-string, use the string "\$". To use the literal backslash (\) in replacement-string, use the string "\\".

flags
An xs:string value that can contain any of the following values that control the matching of pattern to source-string:
s
Indicates that the dot (.) replaces any character.

If the s flag is not specified, the dot (.) replaces any character except the new-line character (X'0A').

m
Indicates that the caret (^) replaces the start of a line (the position after a new-line character), and the dollar sign ($) replaces the end of a line (the position before a new-line character).

If the m flag is not specified, the caret (^) replaces the start of a string, and the dollar sign ($) replaces the end of the string.

i
Indicates that matching is case-insensitive.

If the i flag is not specified, case-sensitive matching is done.

x
Indicates that whitespace characters within pattern are ignored.

If the x flag is not specified, whitespace characters are used for matching.

Limitation of length

The length of source-string, pattern and replacement-string is limited to 32000 bytes.

Returned value

If source-string is not the empty sequence, the returned value is a string that results when the following operations are performed on source-string:
  • source-string is searched for characters that match pattern. If pattern contains two or more alternative sets of characters, the first set of characters in pattern that matches characters in source-string is considered to be the matching pattern.
  • Each set of characters in source-string that matches pattern is replaced with replacement-string. If replacement-string contains any of the variables $0 through $9, the substring of source-string that matches the subexpression in pattern that corresponds to the variable replaces the variable in replacement-string. Then the modified replacement-string is inserted into source-string. If a variable does not have a corresponding subexpression in pattern because there are more variables than subexpressions or a subexpression does not have a match in source-string, a string of length 0 replaces the variable in replacement-string.

If pattern is not found in source-string, an error is returned.

If source-string is the empty sequence, a string of length 0 is returned.

Examples

Example of replacing a substring with another substring: The following function replaces all instances of "a" in the string "abbcacadbdcd" with "ba".
fn:replace("abbcacadbdcd","a","ba")

The returned value is "babbcbacbadbdcd".

Example of replacing a substring using a replacement string with variables: The following function replaces "a" and the character that follows "a" with two instances of the character that follows the "a" in "abbcacadbdcd".
fn:replace("abbcacadbdcd","a(.)","$1$1")

The returned value is "bbbcccddbdcd".