stuff

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

Syntax

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

Parameters

Examples

Usage

  • 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 Transact-SQL Users Guide.

  • If the start position or the length is negative, a NULL string is returned. If the start position is zero or 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).

See also Transact-SQL Users Guide.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute stuff.

Related reference
replicate
substring