The STUFF function

7

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, 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.

About these ads

7 thoughts on “The STUFF function

  1. David Tran says:

    Thank you for the clear explanation, favorited in my favs now :D 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.

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Follow

Get every new post delivered to your Inbox.

Join 527 other followers

%d bloggers like this: