The main idea around the SQL Server function called STUFF is concatenating multiple columns into a single column with more flexibility than the CONCAT function would provide. Besides, STUFF can be combined with other techniques for some interesting effects.
In this article, we’ll explore the possibilities that the STUFF command provides for SQL Database specialists.
The T-SQL STUFF Function
Let’s first take a look at the official Microsoft definition of the STUFF function:
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
Thus, when you are using the STUFF command, you are rearranging the column data.
Let’s take a look at a few examples.
The first one is the output from the STUFF command – the original ABCDEFG string will be stuffed with the XXX string.
We’ll analyze this output to fully understand the STUFF command:
Now, the third parameter is increased to values of 4,5,6.
Function Call Explanations
The replacement happens at the third character of the original ABCDEFG string in every execution. The only difference is the number of characters specified to delete via the delete length parameter.
Thus, when the delete length parameter from the starting position parameter is longer than the original string, the data is truncated.
For example, our original string is 7 characters long. The STUFF function starts at position 3 and runs for 5 more characters. Since 8 is more than the original string length of 7, the value is returned as ABXXX.
Multiple Using of STUFF in the Same Query
You can nest the STUFF command within another STUFF command and utilize it as many times as needed. However, you should consider how it affects the production level queries. Each nested call is running for every row of the data returned.
SELECT STUFF(STUFF('ABCDEFG',1,1,'1'),7,1,'7') as StuffExample5;
In this example, the first character of the original string is replaced by the 1st character, and the last one is replaced by the 7th character.
STUFF and ForXML
ForXML is a feature in SQL Server that can transform the result sets of SQL queries into XML formatted results.
STUFF can also be combined with other SQL Server functionality like ForXML. It helps consolidate data for reporting purposes. Let’s take the following table structure:
Suppose you would like a report with 2 columns. The first column is USER_NAME, and the second is a comma-separated role column for each role the user holds. How could this be accomplished in pure T-SQL?
Build the table structure and insert data:
CREATE TABLE #USER ( USER_ID INT, USER_NAME VARCHAR(50) ) CREATE TABLE #USER_ROLES ( USER_ID INT, ROLE VARCHAR(50) ) INSERT INTO #USER VALUES(1,'Edward') INSERT INTO #USER VALUES(2,'John') INSERT INTO #USER VALUES(3,'Mark') INSERT INTO #USER_ROLES VALUES(1,'Admin') INSERT INTO #USER_ROLES VALUES(1,'Writer') INSERT INTO #USER_ROLES VALUES(1,'Form') INSERT INTO #USER_ROLES VALUES(2,'Writer') INSERT INTO #USER_ROLES VALUES(3,'Form') INSERT INTO #USER_ROLES VALUES(3,'Writer');
Use the STUFF command:
SELECT U.USER_NAME, STUFF((SELECT ',' + UR.ROLE FROM #USER_ROLES UR WHERE UR.USER_ID = U.USER_ID FOR XML PATH('')), 1, 1, '') [ROLES] FROM #USER U GROUP BY U.USER_NAME, U.USER_ID ORDER BY 1
In this example, an inner query is joined to each USER_ID. The STUFF command is used to replace the initial comma. As a result, we have a nice comma-separated list of each user with the respective roles.
SQL Server provides solutions for dynamic data masking. However, STUFF could be used to obfuscate sensitive data when it is displayed. Imagine a view sitting on top of a table that has customer PII fields such as SSN. The view could use the STUFF command to mask everything except the last 4 digits of the SSN.
As demonstrated with FORXML and STUFF, T-SQL offers the ability to consolidate data into single columns with some sort of primary identifier. In this example, it was the USER_NAME. However, this type of analysis is pretty common in database reporting architecture.
Thus, we covered the parameters of the STUFF command in T-SQL and understood how this command compares with CONCAT. We demonstrated both simple and complex examples of the STUFF command and listed a few use cases for which STUFF can be applicable.
It is a valuable command that can be a great tool to have under your belt. Please comment with other use cases for the STUFF command.