SQL Server STUFF() Function

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