SQL Reference

REPLACE

>>-REPLACE--(--expression1--,--expression2--,--expression3--)--><
 

The schema is SYSFUN.

Replaces all occurrences of expression2 in expression1 with expression3.

The first argument can be of any built-in character string or binary string type. 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 second and third arguments are identical to the first argument.

The result of the function is:

• VARCHAR(4000) if the first, second and third arguments are VARCHAR or CHAR
• CLOB(1M) if the first, second and third arguments are CLOB or LONG VARCHAR
• BLOB(1M) if the first, second and third arguments are BLOB.

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:

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

  As mentioned, the output of the REPLACE function is VARCHAR(4000). For the above example the function CHAR has been used to limit the output of REPLACE to 10 bytes.