SQL Server STUFF() Function

The STUFF() function in SqlServer is used to delete a specified length of characters in a given string and insert new characters at the deleted position.

Syntax

STUFF ( character_expression , start , length , replaceWith_expression )

character_expression: Required. The string or expression to be modified.
start: Required. The position at which to delete characters, starting at 1.
length: Required. The length of characters to delete.
replaceWith_expression: Required. The new character or expression to insert.

Usage

The STUFF() function is typically used to replace or remove specified characters in a string. For example, it can be used when you need to remove some characters from a string and replace them with other characters.

Examples

Example 1

Assuming you have the following table:

Id	Name
1	John Smith
2	Alice Green

Now, if you want to replace “Smith” with “Doe” in the name, you can use the following code:

UPDATE table
SET Name = STUFF(Name, CHARINDEX('Smith', Name), 5, 'Doe')
WHERE Id = 1;

After executing the above code, the table will be updated to:

Id	Name
1	John Doe
2	Alice Green

Example 2

Assuming you have the following table:

Id	Name
1	John Smith
2	Alice Green

Now, if you want to remove “Smith” from the name, you can use the following code:

UPDATE table
SET Name = STUFF(Name, CHARINDEX('Smith', Name), 5, '')
WHERE Id = 1;