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.SYSTABLE
WHERE table_id <= 5

The result datatype of a REPLACE function is a LONG VARCHAR. If you use REPLACE in a SELECT INTO statement, you must have a Large Objects Management option license or use CAST and set REPLACE to the correct datatype and size.

There are two ways to work around this issue:

• Declare a local temporary table and then do 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;