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');

For XML with element name

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 &amp. The second result set shows the fixed output. How do we fix it?

XML entities in resultset

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.