Reality check

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

SELECT query with STUFF() function

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.