I used to always see the STUFF function in passing when reading blogs and kind of move past it without understanding what it was doing. I would see it used and think it’s doing something with a bunch of stuff and kind of skip over it since it wasn’t entirely relevant to what else I was reading. When I read about what the STUFF function actually does, it made a lot more sense as a name. More than dealing with “a bunch of stuff” the STUFF function is used for stuffing a string into another string. Let’s check out a few examples of stuffing data.
Not the Thanksgiving Kind
Here’s a pretty straightforward example. What if we want to remove MS in a string and insert the word Microsoft instead? We can use the STUFF function to do that:
SELECT STUFF('This is an example in MS SQL Server.', 23 , 2, 'Microsoft') AS 'EndString'; GO
We go to position 23 in the initial string, specify that we want to remove 2 characters, and then stuff the string with Microsoft. STUFF can be used with variables as well. We can do the following to get the same result as above:
DECLARE @IntialString AS VARCHAR(50) = 'This is an example in MS SQL Server.'; DECLARE @InsertString AS VARCHAR(50) = 'Microsoft'; SELECT STUFF(@IntialString, 23 , 2, @InsertString) AS 'EndString'; GO
Save the Date
You may come across a table that stores dates as VARCHAR or CHAR. Let’s pick some important dates and look at an example of making the date more readable.
CREATE TABLE Holiday ( ID INT IDENTITY(1,1), H_Date VARCHAR(8) ); INSERT INTO Holiday VALUES ('20210101'),('20210404'),('20211031'),('20211225') SELECT H_Date FROM Holiday; GO
What if we want to see the dates with hyphens instead of all numbers:
SELECT STUFF(STUFF(H_Date, 5, 0, '-'), 8, 0, '-') as 'FormattedDate' FROM Holiday; GO
Keep it Comma Separated
Many times I’ve seen STUFF used to combine values into a comma separated list. Let’s create another table and populate it with a few values:
CREATE TABLE Sport ( ID INT IDENTITY(1,1), S_Name VARCHAR(15) ); INSERT INTO Sport VALUES ('Hockey'),('Basketball'),('Football'),('Baseball') SELECT * FROM Sport; GO
If we want to display our sports as a comma separated list, we can use FOR XML PATH:
SELECT STUFF(( SELECT ',' + S_Name FROM Sport FOR XML PATH('') ), 1, 1, '') AS SportComma FROM Sport WHERE ID = 1; GO
We add the WHERE ID = 1 to get only one row returned instead of a row for each of the four values.
If you see that long script just to get a comma separated list and think it’s too complicated, you’re not alone. Thankfully, SQL Server 2017 includes a function called STRING_AGG to make this simple. Using the same data, we can get the same comma separated list result with this query:
SELECT STRING_AGG(S_Name, ',') AS SportAgg FROM Sport; GO
From what I’ve seen, STRING_AGG looks nicer and will perform better as well.
Thanks for reading!