Demystifying STUFF() function


Myth : STUFF() function is only used for concatenation.

Reality : STUFF() function is an important built-in function and can be used for variety of purposes as follows.

  1. Concatenation
  2. Replace specified number of characters
  3. Delete specified number of characters

Reality check

Check here for Microsoft manual about STUFF() function.

I also came across an interesting and useful article SQL Stuff function overview from Rajendra Gupta, that talks about 10 different ways, Stuff() function can be used. It covers Concatenation, Replace and Delete but more specifically on a single string.

In this article, I’ll talk about how Stuff() function can be used on the array of strings.

Let’s start the test

Assume we have a table, that stores the Book Titles, published by various Authors.

Catch here is – it stores the data row wise. But we have to develop a report, that should show all the Book Titles published by each Author in a single row.

We also need to add “Books by Author <author name>: ” before the Book Titles. For e.g. Books by Author A: Book A,Book B

Sample Schema and Data

	, Author		VARCHAR(50)
	, BookTitle		VARCHAR(50)


INSERT INTO ArrayOfString (Author, BookTitle)
VALUES ('Jai', 'Humans')
, ('Jai', 'Roads')
, ('Vijay', 'Incredible India')
, ('Vijay', 'Nature')

SELECT query with STUFF() function

							SELECT ',' + BookTitle 
							FROM ArrayOfString B
							WHERE A.Author = B.Author
							FOR XML PATH ('')
						), 1, 1, 'Books by Author ' + Author + ': '
FROM ArrayOfString A

Output of the SELECT query

In this single query we have done concatenation and replace both. We have concatenated the array of string as comma separated string and replaced the first character i.e. ‘,’ with “Books by Author <author name>:”.

Last argument of Stuff() function is “replaceWith_expression” which if left blank, will delete the specified set of characters.


Stuff() is very useful inbuilt SQL Server function and can be used for variety of purposes. It’s a single function with features of CONCAT, REPLACE, SUBSTRING, LTRIM and most importantly can be used to aggregate multiple rows by the way of concatenation.

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 )

Connecting to %s