Don’t Sweat the Small Stuff()

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!