Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Transact-SQL Set Operators (UNION, EXCEPT and INTERSECT)
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

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

Back to Tip of the Day List Next Tip