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.