Skip to main content

STUFF (SQL)

A string function that replaces a substring within a string.

Synopsis

STUFF(string,start,length,substring)

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.

STUFF cannot use a %Stream.GlobalCharacter field for either the string or substring argument. Attempting to do so generates an SQLCODE -37 error.

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.

Arguments

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.

Examples

The following example shows a single-character substitution, turning BOLT into BELT:

SELECT STUFF('BOLT',2,1,'E')

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,'')

See Also

FeedbackOpens in a new tab