REPLACE function [String]

Replaces all occurrences of a substring with another substring.

REPLACE ( original-string, search-string, replace-string )

If any argument is NULL, the function returns NULL.

If you need to control the width of the resulting column when replace-string is wider than search-string, use the CAST function. For example,

CREATE TABLE aa(a CHAR(5));
INSERT INTO aa VALUES(‘CCCCC’);
COMMIT;
SELECT a, CAST(REPLACE(a,’C’,’ZZ’) AS CHAR(5)) FROM aa;

The following statement returns the value “xx.def.xx.ghi:”

SELECT REPLACE( 'abc.def.abc.ghi', 'abc', 'xx' ) FROM iq_dummy

The following statement generates a result set containing ALTER PROCEDURE statements which, when executed, repair stored procedures that reference a table that has been renamed. (To be useful, the table name must be unique.)

SELECT REPLACE(
  replace(proc_defn,'OldTableName','NewTableName'),
  'create procedure',
  'alter procedure')
FROM SYS.SYSPROCEDURE
WHERE proc_defn LIKE '%OldTableName%'

Use a separator other than the comma for the LIST function:

SELECT REPLACE( list( table_id ), ',', '--')
FROM  SYS.ISYSTAB
WHERE table_id <= 5

The result data type of a REPLACE function is a LONG VARCHAR. If you use REPLACE in a SELECT INTO statement, you must have an Unstructured Data Analytics Option license, or use CAST and set REPLACE to the correct data type and size.

There are two ways to work around this issue:

• Declare a local temporary table, then perform an INSERT:

  DECLARE local temporary table #mytable 
    (name_column char(10)) on commit preserve rows;
  INSERT INTO #mytable SELECT REPLACE(name,'0','1')   FROM dummy_table01;
  

• Use CAST:

  SELECT CAST(replace(name, '0', '1') AS Char(10)) into #mytable from dummy_table01;