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.
- Replace specified number of characters
- Delete specified number of characters
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.
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.