REPLACE function [String]

Function

Replaces all occurrences of a substring with another substring.

Syntax

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

Parameters

If any argument is NULL, the function returns NULL.

original-string The string to be searched. This string 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 replace-string is an empty string, all occurrences of search-string are deleted.

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;

Examples

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

Usage

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:

Standards and compatibility

See also

“SUBSTRING function [String]”