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 needs to 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