InterSystems SQL Reference
REPLACE
|
|
A string function that replaces a substring within a string.
Synopsis
REPLACE(string,oldsubstring,newsubstring)
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,'.','_').
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 in the Concepts section of this manual.
The following example searches for every instance of the substring 'K' and replaces it with the substring 'P':
SELECT REPLACE('KING KONG','K','P')
The following embedded SQL example searches for every instance of the substring 'KANSAS' and replaces it with the substring 'NEBRASKA':
SET str="KANSAS, ARKANSAS, NEBRASKA"
&sql(SELECT REPLACE(:str,'KANSAS','NEBRASKA') INTO :x)
WRITE !,"SQLCODE=",SQLCODE
WRITE !,"Output string=",x
The following example show that
REPLACE handles the empty string ('') just like any other string value:
SELECT REPLACE('','','Nothing'),
REPLACE('KING KONG','','P'),
REPLACE('KING KONG','K','')
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('KING KONG',NULL,'P'),
REPLACE('KING KONG','K',NULL),
REPLACE('KING KONG','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('KING KONG',:a,'P'),
REPLACE('KING KONG','K',:a),
REPLACE('KING KONG','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
Content Date/Time: 2019-02-21 01:13:32