STUFF

STUFF( character_expression , start , length , replaceWith_expression )

• character_expression: This is an expression of character data. This could be a constant, variable, or column of either character or binary data. The data in question is being manipulated by the STUFF function.
• start: This is an integer value which specifies the location at which the function should begin manipulation of the character_expression. The location begins counting from one. If a negative number, zero, or a number beyond the extent of the expression is inputted, an error results.
• length: Length, an integer, specifies the number of characters to be deleted from character_expression starting from the ‘start’ position. If length is negative or zero, no characters are deleted. However, when the sum of start and length exceeds the length of expression, all characters from start to the end of expression are deleted.
• replacewith_expression: This is another character expression. The characters in this expression are inserted into character_expression beginning at the ‘start’ point after the specified number of characters have been deleted. If replaceWith_expression consists of an empty string or is NULL, the function effectively just deletes the specified character range from character_expression.

Example

DECLARE @string VARCHAR(100)
SET @string = 'Hello, World!'
SELECT STUFF(@string, 7, 5, 'SQL')

Output

'Hello, SQL!'

Explanation

The example code represents the use of the STUFF function in SQL Server. The STUFF function substitutes a part of a given string with another string. In this case ‘World’ is replaced by ‘SQL’ in the input string ‘Hello, World!’, resulting in ‘Hello, SQL!’. The parameters for the STUFF function are:

1. The original string
2. The start position for the substitution
3. The length of the substring to be replaced
4. The new string for substitution