STUFF that can make your life easier

Time for some real talk, my friends. When I first started working with SQL Server, I learned how to program in quite possibly the worst way possible – from “experts” who had just been let go from the company I worked at. For a good month, I walked through every bit of SQL code I could find from these guys figuring out all kinds of things in SQL Server.

At some point, I got in the habit of concatenating strings using loops – probably even a few cursors if I’m being painfully honest. Thankfully, at some point, I reversed course and SQL Server added some functionality and now, hopefully, you won’t have to learn from your mistakes as much as I did.

Let’s say we want to concatenate all of the venues that Nirvana played in Florida into one string. The way I did it previously would have been like the following: (Yes, for you experienced SQL developers it may be a bit painful to look at.)

Let’s try to explain all that’s going on here so that everyone is on the same page:

1. We create the @FlVenues variable so that we have the data in one spot. Also notice that it is initially declared as a blank string – this saves us from our selves sometimes.
2. We put all of the data into the #NirvanaInFlorida table so that we have data to work through.
3. We use a WHILE loop to loop through the data in our temp table. The EXISTS statement just checks that there is data in the temp table.
4. We take the concert date from the first row – ordered by the date and put it into another variable. Now this requires that Nirvana only played one concert each day in our temp table.
5. For the date we found in step 4, we find the name of the Venue and add it to our variable we setup in step 1.
6. We now delete that row from our temp table. Note: If we fail to delete the row, this loop will NEVER END!

7. Now that we’re out of the WHILE loop, we delete our temp tables – like the good developers we are.
8. Remove the final comma from our variable.
9. Finally we can look at our data! I placed the length out there so that we can also see the length in this case – just for fun.

Now, let me show you the goodness that last week’s function – STUFF – can do to make this a heck of a lot simpler.

DECLARE @FlVenues VARCHAR(500) = '';
SELECT @FlVenues = STUFF((
		SELECT ', ' + Venue FROM dbo.NirvanaPerformances
		WHERE State = 'FL'
		FOR XML PATH('')
		), 1, 2, '');

SELECT @FlVenues = REPLACE(@FlVenues, '&', '&')
SELECT LEN(@FlVenues), @FlVenues

Now, let’s walk through this last our last batch of code:

1. Declare our variable so that we can work with it later.
2. Use the STUFF function, along with “FOR XML PATH” so that we can treat the data in this subquery as XML
3. Notice that our parameters for the STUFF function is 1, 2, ”. Remember that this will take the first 2 characters of the string and replace it with a blank string.
4. Next we are replacing the ‘&’ with ‘&’. This is because of the “AT&T” venue in our data. Since we are changing it to XML, it does change our item to XML – which needs to be cleaned up for humans to read.
5. Then we can display our length and concatenated string!

As you can tell, this way is way shorter, takes lots fewer resources, and – once you get used to the format – much easier to read! And, even more importantly, the data is the exact same!

And with that my friends, I hope you learned a great use for the STUFF function and can help your own code in the future! Until next time my friends!