Tip of the Day : Example Uses of the STUFF String Function

Welcome to SQL Server Helper !!!

This site is intended for those who are beginning to use SQL Server as part of their day-to-day activities.  You will find in this site a collection of useful functions, triggers, stored procedures and tips and tricks related to SQL Server.

Should you have any comments or questions regarding this site or if you want to ask SQL Server-related questions, e-mail us here.

We hope we are able to help and we are glad to help!!!

SQL Server Tip of the Day - February 17, 2025

Example Uses of the 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 >.

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

SQL Server 2012

SQL Server 2008

User-Defined Functions

Date Functions

A collection of useful user-defined functions that deal with dates.

String Functions

A collection of useful user-defined functions that deal with strings (varchar/char/nvarchar/nchar).

Tree Functions

A collection of useful user-defined functions that deal with tree or hierarchical design structures.

Table-Valued Functions

A collection of useful table-valued user-defined functions that can be used to join with other tables.

SQL Server Built-in Functions

A reference to all built-in functions available within SQL Server grouped into categories.

Tips and Tricks

A collection of useful SQL Server-related tips and tricks:

SQL Server Error Messages

A list of SQL Server error messages and for certain error messages, discusses ways on how to solve the error or work around them:

Frequently Asked Questions