DB2 Version 10.1 for Linux, UNIX, and Windows

translate function

The fn:translate function replaces selected characters in a string with replacement characters.

Syntax


>>-fn:translate(source-string,original-string,replacement-string)-><

source-string

The string in which characters are to be converted.

source-string has the xs:string data type, or is the empty sequence.

original-string

A string that contains the characters that can be converted.

original-string has the xs:string data type.

replacement-string

A string that contains the characters that replace the characters in original-string.

replacement-string has the xs:string data type.

If the length of replacement-string is greater than the length of original-string, the additional characters in replacement-string are ignored.

Limitation of length

The length of original-string and replacement-string is limited to 32000 bytes.

Returned value

If source-string is not the empty sequence, the returned value is the xs:string value that results when the following operations are performed:

For each character in source-string that appears in original-string, replace the character in source-string with the character in replacement-string that appears at the same position as the character in original-string. The characters are matched using a binary comparison.
If the length of original-string is greater than the length of replacement-string, delete each character in source-string that appears in original-string, but the character position in original-string does not have a corresponding position in replacement-string.

If a character appears more than once in original-string, the position of the first occurrence of the character in original-string determines the character in replacement-string that is used.
For each character in source-string that does not appear in original-string, leave the character as it is.

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

Examples

The following function returns the string that results from replacing e with o and l with m in the string 'Test literal'.

fn:translate('Test literal','el','om')

The returned value is 'Tost mitoram'.

The following function returns the string that results from replacing A with B, t with f, e with i, and r with m in the string literal 'Another test literal'.

fn:translate('Another test literal', 'Ater', 'Bfim')

The returned value is 'Bnofhim fisf lifimal'.