Chapter 2: Transact-SQL Functions

stuff

Returns the string formed by deleting a specified number of characters from one string and replacing them with another string.

stuff(char_expr1 | uchar_expr1, start, length, char_expr2 | uchar_expr2)

char_expr1: is a character-type column name, variable, or constant expression of char, varchar, nchar, or nvarchar type.

uchar_expr1: is a character-type column name, variable, or constant expression of unichar or univarchar type.

start: specifies the character position at which to begin deleting characters.

char_expr2: is another character-type column name, variable, or constant expression of char, varchar, nchar, or nvarchar type.

uchar_expr2: is another character-type column name, variable, or constant expression of unichar or univarchar type.

select stuff("abc", 2, 3, "xyz")

---- 
axyz

select stuff("abcdef", 2, 3, null)

go
--- 
aef

select stuff("abcdef", 2, 3, "")

---- 
a ef

stuff, a string function, deletes length characters from char_expr1 or uchar_expr1 at start, then inserts char_expr2 or uchar_expr2 into char_expr1 or uchar_expr2 at start. For general information about string functions, see “String functions”.
If the start position or the length is negative, a NULL string is returned. If the start position is longer than expr1, a NULL string is returned. If the length to be deleted is longer than expr1, expr1 is deleted through its last character (see Example 1).
If the start position falls in the middle of a surrogate pair, start is adjusted to be one less. If the start length position falls in the middle of a surrogate pair, length is adjusted to be one less.
To use stuff to delete a character, replace expr2 with NULL rather than with empty quotation marks. Using ‘‘ ‘’ to specify a null character replaces it with a space (see Eexamples 2 and 3).
If char_expr1 or uchar_expr1 is NULL, stuff returns NULL. If char_expr1 or or uchar_expr1 is a string value and char_expr2 or uchar_expr2 is NULL, stuff replaces the deleted characters with nothing.
If you give a varchar expression as one parameter and a unichar expression as the other, the varchar expression is implicitly converted to unichar (with possible truncation).

ANSI SQL – Compliance level: Transact-SQL extension.

Any user can execute stuff.