STUFF String Function¶

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 >.

Sample Uses of the STUFF String Function¶

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