STUFF (TSQL Function)

Deletes length characters in string_expression, starting from start position, and inserts the specified replacement string there.

Syntax

STUFF ( string_expression , start , length , string_replacement )

Arguments

string_expression

is a string expression of type varchar.

start

specifies the start position (1-based) of the length characters to delete. It is of type int.

length

is the number of characters to delete. It is of type int.

string_replacement

will replace length characters of string_expression beginning at start. It is of type varchar.

Return Types

Returns varchar.

The collation of the returned string is the same as string_expression.

Remarks

If start <= 0 or length < 0, NULL is returned.

If start is longer than string_expression, NULL is returned.

Examples

PRINT STUFF('abcdef',  0, 3, 'HELLO'); -- prints NULL
PRINT STUFF('abcdef',  1, 3, 'HELLO');
PRINT STUFF('abcdef',  2, 3, 'HELLO');
PRINT STUFF('abcdef',  6, 3, 'HELLO');
PRINT STUFF('abcdef',  7, 3, 'HELLO'); -- prints NULL

The result is:

<NULL>
HELLOdef
aHELLOef
abcdeHELLO
<NULL>