Stuff is a function in SQL Server used to perform special operations on a string value.
The below operations can be performed,
- Remove string part from string expression.
- 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);

Nikunj SatasiyaPosted Feb 28, 2019, 4:47 AM
For More Detail of STUFF() in SQL Server, You should visit this URL... I hope that may help you to archive your requirement. https://coding-vila.blogspot.com/2019/02/sql-server-stuff-function-with-syntax-and-example.html
Hadshana KamalanathanPosted Jul 15, 2018, 1:27 PM
Thank you for sharing
Varun SareenPosted Jul 2, 2018, 5:22 AM
Not a good example. Kindly review it and if possible modify it.
B PatelPosted May 31, 2018, 4:10 AM
The last example (example 2) result will not come null.