Replaces a string with another string, and returns the new results.
REPLACE( original-string, search-string, replace-string )
If any argument is NULL, the function returns NULL.
original-string The string to be searched. This can be any length.
search-string The string to be searched for and replaced with replace-string. This string is limited to 255 bytes. If search-string is an empty string, the original string is returned unchanged.
replace-string The replacement string, which replaces search-string. This can be any length. If replacement-string is an empty string, all occurrences of search-string are deleted.
This function replaces all occurrences.
This function supports NCHAR inputs and/or outputs.
SQL/2003 Vendor extension.
The following statement returns the value xx.def.xx.ghi.
SELECT REPLACE( 'abc.def.abc.ghi', 'abc', 'xx' );
The following statement generates a result set containing ALTER PROCEDURE statements which, when executed, would 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.SYSTAB WHERE table_id <= 5;