STUFF

What’s STUFF?

STUFF is a function for manipulating strings. The function “stuffs” one string into another, hence the rather figurative name. Traditionally, string manipulation is not necessarily one of the great strengths of relational databases and SQL syntax, STUFF is one of the functions from this area that, if mastered, can make your life much easier.

What can you do with STUFF?

The STUFF function is passed four arguments:

• the string you want to edit,
• the position from where you want to insert a new text into the string,
• the number of characters you want to replace in the string and
• the string you want to replace it with.

But enough theory, let’s look at the STUFF function in detail. A call to the function might look like this:

SELECT STUFF('Show me your execution plan', 1, 7, 'What''s')

The result of this query is “What’s your execution plan”. We have replaced the first character with seven characters, i.e. “Show me”, and put the string “What’s” in its place. Of course you could solve it that way:

SELECT 'What''s'+SUBSTRING('Show me your execution plan',8,20)

But on the one hand you have to perform rather wild index operations on your strings here, if you don’t insert at the beginning of a string, and on the other hand the readability of such operations is not necessarily the highest. But just replacing text in strings is quite nice, but with the STUFF function we can do much more. Imagine you have given a date in the format YYYYMMDD and you want to make it readable, then of course you can also work with substrings here:

SELECT SUBSTRING('20200514', 1, 4)+'-'+SUBSTRING('20200514',5,2)+'-'+SUBSTRING('20200514',7,2)

At the latest here the trained ninja eye sees that the syntax with STUFF is on the one hand much shorter and on the other hand contains only once the string you are working with, which definitely increases readability and maintainability.

You can also use STUFF to mask data, for example in views. Imagine that you want to return only the first names of your users in an application on the WideWorldImporters database, but mask the last names with X. You can solve this with STUFF as follows:

SELECT 
     FullName
    ,STUFF(
        FullName, 
        CHARINDEX(' ',FullName)+1, 
        LEN(FullName)-CHARINDEX(' ',FullName)+1, 
        REPLICATE('X', LEN(FullName)-CHARINDEX(' ', FullName)))
FROM [Application].[People]

In this query, in the FullName column, replace the characters from the first space to the end of the string with X. This way your names will still have the correct length, but will be masked for external developers, for example, which minimizes potential privacy issues.

However, most of you will probably have encountered the STUFF function when it came to creating a comma-separated list from the values in a column. Let’s take a closer look at how this works.

First we have to try to combine the different lines into a result that we can work with. The SELECT FOR XMLfunctionality is suitable for this, so let’s combine the names of the transaction types in the WideWorldImporters database into one XML object:

SELECT TransactionTypeName 
FROM [Application].[TransactionTypes] 
FOR XML PATH ('')

You now get an XML object that has an entry with the tag “TransactionTypeName” for each transaction type. To get rid of these tags you have to make sure that the column you are working with has no name anymore. You can do this by calling a function or modifying the value of the column. So let’s modify the value by putting a comma in front of it:

SELECT ','+TransactionTypeName 
FROM [Application].[TransactionTypes] 
FOR XML PATH ('')

This almost corresponds to the comma-separated list you are looking for, except that it contains a leading comma. But you can get rid of this with ‘STUFF’ by replacing a character with an empty string starting with character 1. To do this, you need to apply the STUFF function to the result of the query (passing the query as an argument to the STUFF function) and then query the result of the STUFF function to display it. The whole thing looks like this in the result:

SELECT 
    STUFF(
        (SELECT ','+TransactionTypeName
            FROM [Application].[TransactionTypes] 
            FOR XML PATH ('')
        ), 1, 1, '')

If you want to join these values to the rows of a table, you have to adjust the outer select accordingly:

SELECT
    STUFF(
        (SELECT ','+TransactionTypeName
            FROM [Application].[TransactionTypes] 
            FOR XML PATH ('')
        ), 1, 1, '')
FROM [Application].[TransactionTypes]

In today’s dojo you have hopefully learned that the STUFF function can be quite helpful when it comes to replacing, inserting or deleting in strings. So it’s worth taking a look at this feature if your developers come up with some weird requirements regarding string manipulation…

References

• Documentation from Microsoft