REPLACE Function [String]

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.

Parameter	Description
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.

Returns

LONG VARCHAR

LONG NVARCHAR

Note: The result data type 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.

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

Usage

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;

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;

Standards and Compatibility

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Compatible with Adaptive Server Enterprise.