ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

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

Standards and compatibility 

• SQL/92    Vendor extension.

• SQL/99    Vendor extension.

• Sybase    Compatible with Adaptive Server Enterprise.

See also 

SUBSTRING function [String]

Example 

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 would need 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