SQL Reference

REPLACE

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

The schema is SYSFUN.

Replaces all occurrences of expression2 in expression1 with expression3.

The first argument is a character string or binary string with a maximum length of 1048576 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.

[ DB2 List of Books | Search the DB2 Books ]