Background

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

CREATE TABLE ArrayOfString
(
	ID				INT	IDENTITY(1,1) PRIMARY KEY
	, Author		VARCHAR(50)
	, BookTitle		VARCHAR(50)
)

GO

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

SELECT query with STUFF() function

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

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.

Conclusion

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.

• SQL Server