Stuff Function In SQL Server

Stuff is a function in SQL Server used to perform special operations on a string value.

Stuff is a function in SQL Server used to perform special operations on a string value.

The below operations can be performed,

1. Remove string part from string expression.
2. Insert/Append string at specified index.

Syntax

select STUFF(string_value, start_index, no_of_chars_to_replace, replace_string);

Remove String Part

select STUFF('hai_hello',0,2,'');

Important Note

Start Index begins from 1 in STUFF Function.

Proper Index

select STUFF('hai_hello',1,2,'');

Insert String Content

You can insert a string content by specifying the index location and set number of characters to replace to zero. Note that the third parameter value should be zero.

DECLARE @testString varchar(3) = 'abc';
select STUFF('hai_hello', 1, 0, @testString);

Replace String Content

You cannot replace string by specifying the old characters here.

But you can replace the string by specifying start location and number of characters to replace.

Note

The third parameter value should be the length to replace.

Example 1

DECLARE @testString varchar(3) = 'abc';
select STUFF('hai_hello', 1, DATALENGTH (@testString), @testString);

Example 2

select STUFF('hai_hello',1,2,'abc');