Skip to main content
Previous sectionNext section

STUFF

A string function that replaces a substring within a string.

Synopsis

STUFF(string,start,length,substring)

Arguments

Argument Description
string A string expression that is the target for the substring replacement.
start 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.
length 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.
substring 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.

Description

STUFF 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 ('').

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

Specifying NULL for the start, length, or substring argument returns NULL.

REPLACE and STUFF

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.

For a list of functions that search for a substring, refer to String Manipulation in the Concepts section of this manual.

Examples

The following example shows a single-character substitution, turning KING into KONG:

SELECT STUFF('KING',2,1,'O')
Copy code to clipboard

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')
Copy code to clipboard

The following example inserts a substring:

SELECT STUFF('In my old Kentucky home',19,0,' (KY)')
Copy code to clipboard

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 ')
Copy code to clipboard

The following example deletes an 8-character substring by replacing it with the empty string:

SELECT STUFF('In my old Kentucky home',11,8,'')
Copy code to clipboard

See Also