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?
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…