SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

The STUFF function

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:


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.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...