A string function that replaces a substring within a string.
||A string expression that is the target for the substring replacement.
||The starting point for replacement, specified as a positive integer. A character count from the beginning of string, counting from 1. Permitted values are 0 through the length of string. To append characters, specify a start of 0 and a length of 0. The empty string or a nonnumeric value is treated as 0.
||The number of characters to replace, specified as a positive integer. To insert characters, specify a length of 0. To replace all characters after start, specify a length greater than the number of existing characters. The empty string or a nonnumeric value is treated as 0.
||A string expression used to replace the substring identified by its starting point and length. Can be longer or shorter than the substring it replaces. Can be the empty string.
replaces a substring with another substring. It identifies the substring to be replaced by location and length, and replaces it with substring
This function provides compatibility with Transact-SQL implementations.
The replacement substring
may be longer or shorter than the original value. To delete the original value, substring
can be the empty string ('').
value must be within the current length of string
. You can append a substring
to the beginning of string
by specifying a start
value of 0. The empty string or a nonnumeric value is treated as 0.
perform substring replacement. REPLACE
searches for a substring by data value. STUFF
searches for a substring by string position and length.
For a list of functions that search for a substring, refer to String Manipulation
in the Concepts section of this manual.
The following example shows a single-character substitution, turning KING into KONG:
The following examples replace an 8-character substring (Kentucky) with a longer 12-character substring and a shorter 2-character substring:
SELECT STUFF('In my old Kentucky home',11,8,'Rhode Island'),
STUFF('In my old Kentucky home',11,8,'KY')
The following example inserts a substring:
SELECT STUFF('In my old Kentucky home',19,0,' (KY)')
The following example appends a substring to the beginning of the string:
SELECT STUFF('In my old Kentucky home',0,0,'The sun shines bright ')
The following example deletes an 8-character substring by replacing it with the empty string:
SELECT STUFF('In my old Kentucky home',11,8,'')