The STUFF function
17March 14, 2013 by Kenneth Fisher
While I was at the 2011 Pass Summit I was given the following piece of code to create a comma delimited list.
SELECT files.database_id, db.name AS DatabaseName,
STUFF((SELECT ', ' + names.name
FROM sys.master_files names
WHERE names.database_id = files.database_id
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
, 1, 2, '') AS NameList
FROM sys.master_files files
JOIN sys.databases db
ON files.database_id = db.database_id
GROUP BY files.database_id, db.name
Now if you haven’t seen this before take note. It’s awesome.
However, back on point, some months later when I finally sat and broke it down I noticed the function “STUFF” and wondered what on earth it did. Turns out it is one of the more interesting parts of the whole process (at least to me). I’m rather embarrassed to admit I had never seen it before since it has been around since at least SQL 2000.
In this particular case it is being used to strip the first two characters off of the string. Previously I had always used substring like this:
SUBSTRING(field,2,9999999)
I’ve never been totally happy with this because of the length parameter of 9999999. It’s enough characters that my chance of losing any data is slim but it’s not terribly precise. I’ve also used LEN(field)-2, but again I’ve never been total happy.
SUBSTRING(field,2,len(field)-2)
I’m asking for the exact number of characters but I’m now using an extra function and the code looks more complicated, even if not by much.
Using stuff it looks like this:
STUFF(field,1, 2,’’)
No extra functions and I’m being precise in what I’m asking for.
So what exactly does STUFF do? STUFF places one string inside of another string. The first parameter is the string being inserted into and the last string is the string being inserted. The middle two parameters are the position to insert the string and the number of characters to replace with the replacement string. Clear as mud right?
So in our above example at position 1 (the first character) replace the next two characters (positions one and two) with ‘’ or an empty string. Essentially this removes the first two characters of the string in “field”.
Personally I find I can understand new functions & features best by seeing how they work so here are a few examples.
1) I want a string to have the first 6 letters of the alphabet in order. But right now I have abccf.
DECLARE @string varchar(10) SET @string = 'abccf' PRINT STUFF(@string,4,1,'de') --abcdef
This example replaced one character (the second c) with 2 characters (d and e).
2) I still want to have the first 6 letters of the alphabet in order. This time I’m starting with ‘wxyzbcdef’.
DECLARE @string varchar(10) SET @string = 'wxyzbcdef' PRINT STUFF(@string,1,4,'a') --abcdef
This time I replaced 4 characters (wxyz) with 1 character (a).
3) Last example. Still want the same result. But this time I only have ‘af’.
DECLARE @string varchar(10) SET @string = 'af' PRINT STUFF(@string,2,0,'bcde') --abcdef
So in this last example I inserted the string bcde into the second position without deleting any characters from the source string.
SQL Studies 
great..
Thank you for the clear explanation, favorited in my favs now 😀 thanks again!
Very internesting STUFF! Thanks for clearing it out.
clear explanation.thank you
you used a stuff function for “nvarchar” but how to use for “real”
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.
Hi there, do you have an example for using STUFF with numeric data?
I am pretty sure that STUFF is string only. You could CAST to string, use it, then CAST back. Not sure how performance would be though.
Thank you, I found a way to use it for my needs:
,STUFF ((SELECT ‘, ‘ + str(p.[PERPOS_FTE],8,3) FROM PERPOS p WHERE p.PERPOS_HRP_ID = h.HRPER_ID FOR XML PATH(”)),1,1,”) AS PERPOS_FTE
[…] and I plan to continue in the next year. I love looking back over the posts I’ve done, and sometimes I see a post that I’m not terribly impressed with that for whatever reason is really…, and sometimes I’m see a post that impresses the heck out of me even though I wrote it. So […]
great sharing!
Only issue with using the FOR XML PATH(”) is SQL Server will try and convert the string values into proper XML. For example, text with & will convert to &:, causing the data to look funny. If you use the FOR XML EXPLICIT causes you to add a couple of steps, but it doesn’t try to convert the XML. Below is an example.
DECLARE @text VARCHAR(200) = ”
DECLARE @test TABLE
( your_string varchar(25) )
INSERT INTO @test (your_string)
VALUES (‘Red White & Blue’)
, (‘Black’)
, (‘Yellow’)
SELECT ‘, ‘ + your_string
FROM @test
FOR XML PATH(”)
–FIX
SELECT @text = ( SELECT 1 [tag]
, NULL [Parent]
, ‘, ‘ + your_string [Query!1!!CDATA]
FROM @test
FOR XML EXPLICIT )
SELECT @text
— Have to remove the XML tags
SET @text = REPLACE(REPLACE(@text, ”, ”)
SELECT @text
I’ll have to remember that 🙂 Thanks.
[…] last but not least we use my favorite text function STUFF to remove a piece (eight characters long, starting at the second character) of the contents of the […]
I am stuck with a issue, Currently I am using STUFF but seems like it is limiting by the size. When I look at the actual text I have more than 1 million records but after STUFFing I loose almost half of the characters…
Here is the code—-
SELECT B.CASE_NO, B.zTEXT_CASE, STUFF ((SELECT CHAR(10) + CAST(NOTE_DATE AS nvarchar(max)) + ' ' + CAST(A.NOTES AS nvarchar(max)) FROM dbo.stHCM_CRM_ZIRW_RMSIMR_CASES_CASE_NOTE AS A WHERE (A.CASE_ID = B.CASE_ID) GROUP BY CN_ID, NOTE_DATE, CAST(NOTES AS nvarchar(max)) ORDER BY CN_ID FOR xml path(''), TYPE ).value('.', 'nvarchar(max)'), 1, 1, '') AS zTEXT_CASENew FROM dbo.stHCM_CRM_ZIRW_RMSIMR_CASES AS B WHERE B.zActive = 1 AND B.CASE_ID = '3891'Any help or feedback would be greatly appreciated.
Have you tried it without the stuff? To make sure that’s actually what the problem is?
[…] set printing each row out, which again goes against the whole batch thing. Which leads me to a simple piece of code that creates a delimited list. In this case, I’m delimiting by a carriage return so each row […]