DB2 Version 10.1 for Linux, UNIX, and Windows

REPLACE (SYSFUN schema) scalar function

Replaces all occurrences of expression2 in expression1 with expression3.

Read syntax diagramSkip visual syntax diagram
>>-REPLACE -(--expression1--,--expression2--,--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.

In a Unicode database, if a supplied argument is a graphic string, it is first converted to a character string before the function is executed. For a VARCHAR, the maximum length is 4 000 bytes, and for a CLOB or a binary string, the maximum length is 1 048 576 bytes. CHAR is converted to VARCHAR and LONG VARCHAR is converted to CLOB(1M).

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

Example

Replace all occurrence of the letter 'N' in the word 'DINING' with 'VID'. 
   VALUES CHAR (REPLACE ('DINING', 'N', 'VID'), 10)
This example returns the following output: 
   1
   ----------
   DIVIDIVIDG
The output of the REPLACE function is VARCHAR(4000). For this example, the CHAR function has been used to limit the output of REPLACE to 10 bytes.