SQL – Stuff Function

This is the most amazing function of T-SQL which is mostly used to delete a specified length of characters within a given string and replace it with another set of characters. Unlike the CONCAT function, which was introduced in SQL Server 2012, the STUFF function has been in prior versions of SQL Server too and hence we can use it in them too.

Syntax:- STUFF (Character Expression, Start, Length, Replace With Expression) Arguments: This function uses the following parameters. Character Expression: Is an expression of character data. Character Expression can be a constant, variable, or column of either character or binary data. Start: Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first Character Expression, a null string is returned. Start can be of type bigint. Length: Is an integer that specifies the number of characters to delete. If length is longer than the first Character Expression, deletion occurs up to the last character in the last Character Expression. Length can be of type bigint. Replace With Expression: Is an expression of character data. Replace With Expression can be a constant, variable, or column of either character or binary data. This expression will replace length characters of Character Expression beginning at start.

Important points to remember: There are some basic points always keep in mind as given below- 

1. If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. 
2. If the start position is 0, a null value is returned. 
3. If the length to delete is longer than the first string, it is deleted to the first character in the first string.

Important Facts - Unfortunately the stuff function only works on “strings” (char, nchar, varchar, nvarchar). If you need to use it on a numeric data type you will have to convert it to a string and back again.
Example 1: Generate a Comma-Separated List
Stuff function is very useful if we want to add comma-separated list. If we want to capture all comments against any particular topic then stuff function comes into the picture such as given below-

--- declare table variable to store the comments DECLARE @UserInputs TABLE (       PollId Int, PollSubject Varchar(250), UserComments Varchar(250) ) ----- Insert Values into table variable INSERT INTO @UserInputs (  PollId, PollSubject, UserComments) VALUES (1, 'Most favourite  super hero?', 'Superman' ), (1, 'Most favourite  super hero?' ,'Batman' ), (1, 'Most favourite  super hero?' ,'Ironman'), (1, 'Most favourite  super hero?' ,'Wolverine'), (2, 'Most favourite  movie?', 'Titanic' ), (2, 'Most favourite  movie?' ,'The Note Book' ), (3, 'Most favourite  Game?' ,'Cricket'), (3, 'Most favourite  Game?' ,'Football')  ----- Table Variable output SELECT PollId, PollSubject, UserComments FROM @UserInputs PollId PollSubject UserComments 1 Most favourite  super hero? Superman 1 Most favourite  super hero? Batman 1 Most favourite  super hero? Ironman 1 Most favourite  super hero? Wolverine 2 Most favourite  movie? Titanic 2 Most favourite  movie? The Note Book 3 Most favourite  Game? Cricket 3 Most favourite  Game? Football  ----- Comments by using stuff function SELECT DISTINCT PollId, PollSubject, UserInput=STUFF((SELECT ',' + UserComments                                                         FROM @UserInputs                                                         Where PollId=UI.PollId                                                         ORDER BY PollSubject                                                         FOR XML PATH('')), 1, 1, '') from @UserInputs UI ORDER BY UI.PollId PollId PollSubject Output 1 Most favourite super hero? Superman,Batman,Ironman,Wolverine 2 Most favourite  movie? Titanic,The Note Book 3 Most favourite  Game? Cricket,Football

We are aware that all that STUFF is doing is trimming the leading , off of the text that FOR XML PATH is generating.
Example 2: Insert One String Into Another String at a Specific Location
We can use the stuff function to replace or insert new string into the existing string as given below:

---- declare table variable to store the comments DECLARE @UserInputs TABLE (       PollId Int, PollSubject Varchar(250), UserComments Varchar(250) ) ----- Insert Values into table variable INSERT INTO @UserInputs (  PollId, PollSubject, UserComments) VALUES (1, 'Most favourite  super hero is ? for kids', 'Superman' ), (2, 'Most favourite  movie is ? in Cinema', 'Titanic'), (3, 'Most favourite  Game is ? in the world.' ,'Cricket') ----- Table Variable output SELECT PollId, PollSubject, UserComments FROM @UserInputs PollId PollSubject UserComments 1 Most favourite  super hero is ? for kids Superman 2 Most favourite  movie is ? in Cinema Titanic 3 Most favourite  Game is ? in the world. Cricket ----- Insert One String Into Another String at a Specific Location ----- by using stuff function SELECT PollId ,UserInputs=STUFF(PollSubject, CHARINDEX('?', PollSubject), 1, UserComments)  FROM @UserInputs  ORDER BY PollId PollId UserInputs 1 Most favourite  super hero is Superman for kids 2 Most favourite  movie is Titanic in Cinema 3 Most favourite  Game is Cricket in the world.

SQL Stuff () Vs REPLACE()

One of the more important aspects of STUFF and at least one huge difference between STUFF and REPLACE when both are used to replace things.
For example, STUFF can stuff a string into an existing string without replacing any of the original string. REPLACE cannot.

REPLACE will replace ALL instances of the search string with the replacement string in the original string. STUFF only does one instance and must be tightly controlled by position rather than by existence.

Stuff () - This function can be used for delete a certain length of the string and insert a new string in the deleted place.
STUFF Syntax: STUFF (String, StartPos, LengthofReplaceChar, ReplaceString)
String - String to be overwritten
StartPos - Starting Position for overwriting
LengthofReplaceChar - Length of replacement string
ReplaceString - String to overwrite

REPLACE()- This function replaces all the occurrences of a string expression with a new string within an input string. 
REPLACE Syntax: REPLACE (String, StringToReplace, StringTobeReplaced)
String - Input String
StringToReplace - The portion of string to replace
StringTobeReplaced - String to overwrite
Conclusion

STUFF is likely to be more flexible. Just depends on your needs. The STUFF string function inserts a string into another string.  It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.