SQL Stuff Function

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.

Table of contents

• Definition of the STUFF() function
• Function Call Explanations
• Multiple Using of STUFF in the Same Query
• STUFF and ForXML
• Use Cases
• Summary

Definition of the 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.

SELECT STUFF('ABCDEFG',3,3,'XXX');

We’ll analyze this output to fully understand the STUFF command:

Now, the third parameter is increased to values of 4,5,6.

SELECT STUFF('ABCDEFG',3,4,'XXX');

SELECT STUFF('ABCDEFG',3,5,'XXX');

SELECT STUFF('ABCDEFG',3,6,'XXX');

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 1^st character, and the last one is replaced by the 7^th 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.

Use Cases

Data Masking

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.

Data Consolidation

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.

Summary

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.

Tags: sql functions, t-sql Last modified: October 28, 2022