REPLACE (SQL)

A string function that replaces a substring within a string.

Synopsis

REPLACE(string,oldsubstring,newsubstring)

Description

REPLACE searches a string for a substring and replaces all matches. Matching is case-sensitive. If a match is found, it replaces every instance of oldsubstring with newsubstring. The replacement substring may be longer or shorter than the substring it replaces. If the substring cannot be found, REPLACE returns the original string unchanged.

The value returned by REPLACE is always of data type VARCHAR, regardless of the data type of string. This allows for replacement operations such as REPLACE(12.3,'.','_').

REPLACE cannot use a %Stream.GlobalCharacter field for the string, oldsubstring, or newsubstring argument. Attempting to do so generates an SQLCODE -37 error.

The empty string is a string value. You can, therefore, use the empty string for any argument value. However, note that the ObjectScript empty string is passed to InterSystems SQL as NULL.

NULL is not a data value in InterSystems SQL. For this reason, specifying NULL for any of the REPLACE arguments returns NULL, regardless of whether or not a match occurs.

This function provides compatibility with Transact-SQL implementations.

REPLACE, STUFF, and $TRANSLATE

Both REPLACE and STUFF perform substring replacement. REPLACE searches for a substring by data value. STUFF searches for a substring by string position and length.

REPLACE performs a single string-for-string matching and replacement. $TRANSLATE performs character-for-character matching and replacement; it can replace all instances of one or more specified single characters with corresponding specified replacement single characters. It can also remove all instances of one or more specified single characters from a string.

By default, all three functions are case-sensitive and replace all matching instances.

For a list of functions that search for a substring, refer to String Manipulation.

Arguments

string

A string expression that is the target for the substring search.

oldsubstring

The substring to match within string.

newsubstring

The substring used to replace oldsubstring.

Examples

The following example searches for every instance of the substring 'P' and replaces it with the substring 'K':

SELECT REPLACE('PING PONG','P','K')

The following example searches for every instance of the substring 'KANSAS' and replaces it with the substring 'NEBRASKA':

SELECT REPLACE('KANSAS, ARKANSAS, NEBRASKA','KANSAS','NEBRASKA')

The following example show that REPLACE handles the empty string ('') just like any other string value:

SELECT REPLACE('','','Nothing'),
       REPLACE('PING PONG','','K'),
       REPLACE('PING PONG','P','')

The following example shows that REPLACE handles any NULL argument by returning NULL. All of the following REPLACE functions return NULL, including the last, in which no match occurs:

SELECT REPLACE(NULL,'K','P'),
       REPLACE(NULL,NULL,'P'),
       REPLACE('PING PONG',NULL,'K'),
       REPLACE('PING PONG','P',NULL),
       REPLACE('PING PONG','Z',NULL)

The following Embedded SQL example is identical to the previous NULLs example. It shows how the ObjectScript empty string host variable is treated as NULL within SQL:

  SET a=""
  &sql(SELECT 
  REPLACE(:a,'K','P'),
  REPLACE(:a,:a,'P'),
  REPLACE('PING PONG',:a,'K'),
  REPLACE('PING PONG','P',:a),
  REPLACE('PING PONG','Z',:a)
  INTO :v,:w,:x,:y,:z)
  WRITE !,"SQLCODE=",SQLCODE
  WRITE !,"Output string=",v
  WRITE !,"Output string=",w
  WRITE !,"Output string=",x
  WRITE !,"Output string=",y
  WRITE !,"Output string=",z