The STUFF function


March 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, AS DatabaseName,
		STUFF((SELECT ', ' +
				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,

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:


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.


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')

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')

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')

So in this last example I inserted the string bcde into the second position without deleting any characters from the source string.

17 thoughts on “The STUFF function

  1. David Tran says:

    Thank you for the clear explanation, favorited in my favs now 😀 thanks again!

  2. Doe says:

    Very internesting STUFF! Thanks for clearing it out.

  3. Jo says:

    clear explanation.thank you

  4. Kavin says:

    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.

      • Dorothy Mann says:

        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.

        • Dorothy Mann says:

          Thank you, I found a way to use it for my needs:


  5. […] 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 […]

  6. Pete Miller says:

    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 &amp:, 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) = ”

    ( your_string varchar(25) )

    INSERT INTO @test (your_string)
    VALUES (‘Red White & Blue’)
    , (‘Black’)
    , (‘Yellow’)

    SELECT ‘, ‘ + your_string
    FROM @test


    SELECT @text = ( SELECT 1 [tag]
    , NULL [Parent]
    , ‘, ‘ + your_string [Query!1!!CDATA]
    FROM @test

    SELECT @text

    — Have to remove the XML tags
    SET @text = REPLACE(REPLACE(@text, ”, ”)

    SELECT @text

  7. […] 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 […]

  8. PK says:

    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 CHAR(10) + CAST(NOTE_DATE AS nvarchar(max)) + ' ' + CAST(A.NOTES AS nvarchar(max))
                  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
    WHERE B.zActive = 1 AND B.CASE_ID = '3891'

    Any help or feedback would be greatly appreciated.

  9. […] 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 […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: