In this post, I will explain the SQL Server STUFF function.
STUFF function is used to insert or replace the part of a string with some other string. Based on your requirement, you can either insert a second string into the main string or replace a part of the main string with a second string. STUFF function deletes a specified length of the characters in the main string at the specified start position and then inserts the second string into the main string at the specified start position.
Syntax:
STUFF (character_expression , start , length , replaceWith_expression)
- character_expression - indicates source string to modify (main string)
- start - indicates start position to insert or replace
- length - indicates the number of character to replace (specify 0 in case of insertion)
- replaceWith_expression - indicates the string to either insert or replace
Examples
Let's look at some SQL Server STUFF function examples-
SELECT STUFF('11,12,13', 1, 0, 'Insert')
Result – ‘Insert11,12,13’
SELECT STUFF('11,12,13', 4, 0, 'Insert')
Result – ‘11,Insert12,13’
SELECT STUFF('11,12,13', 1, 2, 'Replace')
Result – ‘Replace,12,13’
SELECT STUFF('11,12,13', 4, 2, 'Replace')
Result – ‘11,Replace,13’
SELECT STUFF('11,12,13', 3, 3, '')
Result - 11,13