Using STUFF function in SQL

The STUFF string function inserts a string into another string. 

Syntax:-

STUFF ( <StringExpression>, <StartIndex>, <LengthtoBeReplaced>,<StringExpression>)

It can be useful in below cases,

1.     Insert One String Into Another String at a Specific Location

2.     Format Time From HHMM to HH:MM

3.     Format Date from MMDDYYYY to MM/DD/YYYY

4.     Put Spaces or Commas Between Letters in a String

5.     Mask a Credit Card Number

DECLARE @CreditCardNumber        VARCHAR(20)

SET @CreditCardNumber = '445781247811237'

SELECT STUFF(@CreditCardNumber, 1, LEN(@CreditCardNumber) - 4,

REPLICATE('X', LEN(@CreditCardNumber) - 4)) AS MaskedOutPut

Result:-

MaskedOutPut

----------------------

XXXXXXXXXXX1237

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 OutPutColunmName

Result:-

OutPutColunmName

------------------------------------------------------

Batman,Ironman,Superman,Wolverine

So this is common use of STUFF, other than this it can be useful to get values from different rows or column as common separated values as,

Getting multiple row values as comma separated string

LanguageID	LanguageName
1	English
2	Spanish
3	French
4	German

SELECT STUFF((SELECT ',' +  LanguageName

FROM tblLanguage WHERE LanguageID in (1,2,3)         

      FOR XML PATH('')), 1, 1, '') as SupportedLanguage

Result:-

SupportedLanguage

-----------------------------------------

English,Spanish,French

Semilarly, more colunm values with comma sepration and semicolon between rows values cane be done as follows,

SELECT STUFF((SELECT ';' + CAST (LanguageID as varchar(max)) + ','

+ LanguageName FROM Ref_ tblLanguage WHERE LanguageID in (1,2,3)         

            FOR XML PATH('')), 1, 1, '') as SupportedLanguage

Result:-

SupportedLanguage

-----------------------------------------

1,English;2,Spanish;3,French

You can use join or SQL of your choice here to get desired result.

For complete article refer, http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=3934817c-1a03-4ac9-a0ba-55b2bfbaea0f&tkw=uses-of-the-stuff-string-function