The STUFF function in SQL Server is one of those little gems that is very under-used but when needed can be a real handy utility – at least that’s how it appears to me.
The STUFF function allows you to replace specific characters from a string with the contents of another string – we have to identify these characters using a number, signifying where to start and how many characters to replace.
At this stage, you might be wondering why the function is not called REPLACE – great questions. Turns out that T-SQL already has a REPLACE function, so (obviously 
) STUFF became the name for his one.
The function basically takes a string, the number of the character to being the string removal from, the length of the string to remove and another string that represents the characters that should be used to STUFF into the original string.
Here an example.
Let’s turn the sting “Your welcome” into the string “You’re welcome” using T-SQL and the STUFF function.
DECLARE @STRING NVARCHAR(20) = 'Your Welcome';
SELECT STUFF(@STRING , 4,1,'''re') AS Correction;
And we get
The strings can even be the result of SELECT statements themselves
SELECT STUFF((SELECT 'Your Welcome') , 4,1,(SELECT '''re')) AS [Correction(Again)];
The replacement string can even be an empty string – in which case we are essentially removing a character. I’ve found this really useful when concerning comma separated strings and then either removing the first or last comma.
Here’s a example (removing the leading comma)
SELECT STUFF((SELECT ',Martin,Julie,John,Bert,Stephen,Jean,Claire') , 1,1,'') AS [First Comma removed];
And we get
Hope somebody finds this useful
Have a great day
Cheers
Martin