--===============================================================
SQL 2012 - Funções STUFF
--===============================================================
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:
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 #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
https://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=3934817c-1a03-4ac9-a0ba-55b2bfbaea0f&tkw=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:
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 #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
https://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=3934817c-1a03-4ac9-a0ba-55b2bfbaea0f&tkw=uses-of-the-stuff-string-function