SQL - STUFF() Function
This is the most amazing function of T-SQL which is used to delete a specified length of characters within a string and replace with another set of characters.
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-
- 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.
- If the start position is 0, a null value is returned.
- 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()
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
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.
