The STUFF string 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. The syntax of the STUFF string function is as follows:
STUFF ( < character_expression >, < start >, < length >,
< character_expression > )
The < character_expression > parameter is of character data and can be a constant, variable or column of either character or binary data. The < start > parameter is an integer value that specifies the location to start deletion and insertion, which can be of type BIGINT. If < start > or < length > parameter is negative, a NULL string is returned. If the < start > parameter is longer than the first < character_expression >, a NULL string is also returned. The < length > parameter, which can be of type BIGINT, is an integer that specifies the number of characters to delete. If < length > is longer than the first < character_expression >, deletion occurs up to the last character in the last < character_expression >.
Here are sample uses of the STUFF string function
Usage #1 : Insert One String Into Another String at a Specific Location
DECLARE @FullName VARCHAR(100)
DECLARE @Alias VARCHAR(20)
SET @FullName = 'Clark Kent'
SET @Alias = ' "Superman" '
SELECT STUFF(@FullName, CHARINDEX(' ', @FullName), 1, @Alias) AS [FullName]
Output
---------------
Clark "Superman" Kent
Usage #2 : Format Time From HHMM to HH:MM
DECLARE @Time VARCHAR(10)
SET @Time = '1030'
SELECT STUFF(@Time, 3, 0, ':') AS [HH:MM]
Output
------------
10:30
Usage #3 : Format Date from MMDDYYYY to MM/DD/YYYY
DECLARE @MMDDYYYY VARCHAR(10)
SET @MMDDYYYY = '07042013'
SELECT STUFF(STUFF(@MMDDYYYY, 3, 0, '/'), 6, 0, '/') AS [MM/DD/YYYY]
Output
------------
07/04/2013
Usage #4 : Put Spaces or Commas Between Letters in a String
DECLARE @String1 VARCHAR(100)
DECLARE @String2 VARCHAR(100)
SET @String1 = 'ABCDEFGHIJ'
SET @String2 = 'ABCDEFGHIJ'
SELECT @String1 = STUFF(@String1, [Number] * 2, 0, ' '),
@String2 = STUFF(@String2, [Number] * 2, 0, ',')
FROM [master].[dbo].[spt_values]
WHERE [Type] = 'P' AND
[Number] BETWEEN 1 AND 9
SELECT @String1 AS [Output1], @String2 AS [Output2]
Output1 Output2
-------------------- ---------------------
A B C D E F G H I J A,B,C,D,E,F,G,H,I,J
Usage #5 : Mask a Credit Card Number
DECLARE @CreditCardNumber VARCHAR(20)
SET @CreditCardNumber = '4111111111111111'
SELECT STUFF(@CreditCardNumber, 1, LEN(@CreditCardNumber) - 4,
REPLICATE('X', LEN(@CreditCardNumber) - 4)) AS [Output]
Output
------------
XXXXXXXXXXXX1111
Usage #6 : Generate a Comma-Separated List
DECLARE @Heroes TABLE (
[HeroName] VARCHAR(20)
)
INSERT INTO @Heroes ( [HeroName] )
VALUES ( 'Superman' ), ( 'Batman' ), ('Ironman'), ('Wolverine')
SELECT STUFF((SELECT ',' + [HeroName]
FROM @Heroes
ORDER BY [HeroName]
FOR XML PATH('')), 1, 1, '') AS [Output]
Output
------------
Batman,Ironman,Superman,Wolverine