What is STUFF in SQL?
The STUFF function enables you to delete some characters from a string and then insert (or 'stuff') some new characters into the string at the same position in the string where you removed the original characters from. Learn how to use the function in this article by following a number of clear, concise examples.
SYNTAX
STUFF(original_string, start_position, length, new_characters)
Function parameters
- original_string - the string that you want to amend
- start_position - the position at which to start deleting characters from the string
- length - the number of characters to remove
- new_characters - the new characters to insert into the string at the start_position. The new_characters can be a string, constant, column data. You can also pass an empty string or NULL so no new characters will be stuffed into the original string
Examples of using the STUFF function
-- Start removing characters from the 5th character (the 'B')
-- Remove 5 characters ('Book ')
-- Don't insert any new characters (the 4th parameter is an empty string)
-- Give the value a column heading of 'StuffedMessage'
SELECT STUFF('SQL Book Rocks', 5, 5, '') As StuffedMessage;
Returns:
StuffedMessage |
---|
SQL Rocks |
Example 2
-- Delete the space after Hello (6th character, 1 character in length)
-- Insert string ' wonderful ' at the start position
SELECT STUFF('Hello world', 6, 1, ' wonderful ') As Greeting;
Greeting |
---|
Hello wonderful world |
Example 3 - Start position = 0
-- If start_position parameter is 0 or negative the function returns NULL
SELECT STUFF('Hello', 0, 1, '') As LowStart;
LowStart |
---|
NULL |
Example 4 - Start position > string length
-- If start position parameter is greater than the length of the string than NULL is returned
SELECT STUFF('Hello', 7, 1, '') As HighStart
HighStart |
---|
NULL |
Example 5 - length is negative
-- If length is negative the function returns NULL
SELECT STUFF('Hello', 4, -3, '') As NegativeLength
NegativeLength |
---|
NULL |
Example 6 - length is greater than the length of the string
-- If length is greather than the length of original_string
-- then it removes all characters from start position the end of the string
SELECT STUFF('Hello', 4, 10, '') As RemoveToEnd
RemoveToEnd |
---|
Hel |
Example 7 - length = 0
-- If length is 0 then no characters are
-- removed and new_characters are inserted at the start position
SELECT STUFF('Prime', 5, 0, 'itiv') As NewWord;
NewWord |
---|
Primitive |
Example 8 - remove a leading comma
-- Example to remove a leading comma
SELECT STUFF(',Freddie,Brian,John,Roger', 1, 1, '') As 'QueenMembers';
QueenMembers |
---|
Freddie,Brian,John,Roger |
Using STUFF with the FOR XML clause to concatenate column values with a grouping column
STUFF is commonly used with the FOR XML clause when selecting data to build a comma-separated list of values from a column that is grouped by another column.
Let see an example of this. First, we will create a simple table.
DECLARE @T TABLE (
SalesRep varchar(30),
Territory varchar(30)
)
INSERT INTO @T
VALUES ('Jim', 'Alaska'),
('Jim', 'Hawaii'),
('Jane', 'Texas'),
('Jane', 'Canada'),
('Jane', 'Alabama');
SELECT* FROM @T;
SalesRep | Territory |
---|---|
Jim | Alaska |
Jim | Hawaii |
Jane | Texas |
Jane | Canada |
Jane | Alabama |
What we want to achieve is a row for each SalesRep with a comma-delimited list of the Territories that they cover.
SalesRep | TerritoriesCovered |
---|---|
Jane | Alabama,Canada,Texas |
Jim | Alaska,Hawaii |
Let's see how we can achieve this with STUFF and FOR XML and then we will break it down to explain how it works.
SELECT SalesRep,
TerritoriesCovered = STUFF(
(SELECT ',' + Territory
FROM @T As T2
WHERE T1.SalesRep = T2.SalesRep
ORDER BY Territory
FOR XML PATH(''))
, 1, 1, ''
)
FROM @T As T1 GROUP BY SalesRep
ORDER BY SalesRep
SalesRep | TerritoriesCovered |
---|---|
Jane | Alabama,Canada,Texas |
Jim | Alaska,Hawaii |
To break this down piece by piece, let's look at how the FOR XML PATH clause works here.
If we add FOR XML PATH to the end of a SELECT statement the results will be formatted in XML and each row will be wrapped with what is passed in the brackets after the FOR XML PATH.
SELECT ',' + Territory FROM @T FOR XML PATH ('territory');
Generate comma-delimited list
To remove the territory element we can pass in a blank string so we just get a comma-delimited list of values:
SELECT ',' + Territory FROM @T FOR XML PATH ('');
,Alaska,Hawaii,Texas,Canada,Alabama |
Remove the leading comma with STUFF
We can take this and then remove the leading comma with STUFF as we did in example 8 above:
SELECT Territories = STUFF((
SELECT ',' + Territory FROM @T FOR XML PATH('')
), 1, 1, '');
Territories |
---|
Alaska,Hawaii,Texas,Canada,Alabama |
Join on the column we want to aggregate by
At the minute we are returning all territories from all of our sales reps. We want to have a list for each sales rep. To do that we will perform an INNER JOIN on the column that we want to 'group' the territories by. In our example, we want to use the SalesRep column to join on and GROUP BY.
SELECT Territories = STUFF((
SELECT ',' + Territory
FROM @T As T2
WHERE T1.SalesRep = T2.SalesRep
FOR XML PATH('')
), 1, 1, '')
FROM @T As T1
GROUP BY SalesRep
Territories |
---|
Texas,Canada,Alabama |
Alaska,Hawaii |
Order the results
The last thing to do is add on the SalesRep column and ORDER the results.
SELECT SalesRep,
TerritoriesCovered = STUFF(
(SELECT ',' + Territory
FROM @T As T2
WHERE T1.SalesRep = T2.SalesRep
ORDER BY Territory
FOR XML PATH(''))
, 1, 1, ''
)
FROM @T As T1 GROUP BY SalesRep
ORDER BY SalesRep
Fix XML entity encoding
By default, the FOR XML PATH clause returns XML and this means that any characters that are reserved characters in XML such as the ampersand (&) are converted to their XML entity value. For & this would be & This is shown in the resultset below if we added a new row to our table with the Territory of 'North & East'. You can see in the following resultset the & is returned as &. The second result set shows the fixed output. How do we fix it?
To fix it we add
,TYPE).value('.', 'varchar(max)')
which converts the xml type result to a varchar(max) type and so doesn't encode any XML reserved characters.
SELECT SalesRep,
TerritoriesCovered = STUFF(
(SELECT ',' + Territory
FROM @T As T2
WHERE T1.SalesRep = T2.SalesRep
ORDER BY Territory
FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
, 1, 1, ''
)
FROM @T As T1 GROUP BY SalesRep
ORDER BY SalesRep
Conclusion
In this article we have seen how STUFF is a fairly simple string function that can literally stuff a string into another string.
It is, however, when you combine STUFF with other functions and clauses that you can see the power it has to solve some important real-world data querying scenarios.