DB2 Scalar functions - REPLACE (SYSFUN schema)

REPLACE (SYSFUN schema)

Replaces all occurrences of expression2 in expression1 with expression3.

The schema is SYSFUN.

The search is done using a binary comparison with no special consideration for multi-byte characters.

expression1 or expression2 or expression3

The data type for the arguments can be of any built-in character string or binary string type.

The expression must return a built-in character string, Boolean value, or binary string.
In a Unicode database, the expression can also return a graphic string, in which case it is first converted to a character string before the function is evaluated.
The maximum length is:

4000 bytes for a VARCHAR
1,048,576 bytes for a CLOB or binary string

A CHAR value is converted to VARCHAR and a LONG VARCHAR value is converted to CLOB(1M).

Result

The data type of the result is VARCHAR(4000).

The result can be null; if any argument is null, the result is the null value.

Examples

Replace all occurrences of the letter 'N' in the word 'DINING' with 'VID':

   VALUES REPLACE ('DINING', 'N', 'VID')

This example returns the following VARCHAR(4000) output:

   1
   ----------
   DIVIDIVIDG

Replace all occurrences of the letter 'N' in the word 'DINING' with 'VID', and limit the output to 9 bytes:

   VALUES CHAR (REPLACE ('DINING', 'N', 'VID'), 9)

This example returns the following CHAR(9) output:

   1
   ----------
   DIVIDIVID