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

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

Remove String Part

  1. select STUFF('hai_hello',0,2,'');
SQL Server

Important Note

Start Index begins from 1 in STUFF Function.

Proper Index

  1. 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.

  1. DECLARE @testString varchar(3) = 'abc';
  2. 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

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

Example 2

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