STUFF (T-SQL)

Deletes a specified length of characters and inserts another set of characters at a specified starting point.

Syntax

STUFF(character_expression, start, length, character_expression)

Arguments

character_expression: Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.
start: Is an integer value that specifies the location to begin deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned.
length: Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression.

Return Types

Returns character data if character_expression is one of the supported character data types. Returns binary data if character_expression is one of the supported binary data types.

Remarks

String functions can be nested.

Examples

This example returns a character string created by deleting three characters from the first string (abcdef) starting at position 2 (at b) and inserting the second string at the deletion point.

SELECT STUFF('abcdef', 2, 3, 'ijklmn')

GO

Here is the result set:

---------

aijklmnef