Description

STUFF function deletes a sequence of characters from a source string and then inserts another sequence of characters into the source string, starting at a specified position.

Syntax

STUFF (InputString, start, length, ReplacedString)

Example

SELECT Result = STUFF('Google.com', 8, 3, 'Coder007.com');

Output

Google.Coder007.com

Real World Example

create table Tbl_Stuff(ID int, Name nvarchar(50))

insert into Tbl_Stuff values(1, 'Surajit')
insert into Tbl_Stuff values(1, 'Amit')
insert into Tbl_Stuff values(1, 'Abhijit')
insert into Tbl_Stuff values(1, 'Sourav')
insert into Tbl_Stuff values(1, 'Asis')

Query-1

SELECT Result = STUFF((SELECT ',' + SUB.Name AS [text()]
                        FROM Tbl_Stuff SUB
                        WHERE SUB.ID = ID
                        FOR XML PATH('')
                        ), 1, 1, '' )

Output

  Result
  Surajit,Amit,Abhijit,Sourav,Asis

Query-2

SELECT distinct ID,
  STUFF((SELECT ',' + Name 
  from Tbl_Stuff p 
  WHERE p.ID = ID 
  FOR XML PATH('')), 1, 1, '') as Result
FROM Tbl_Stuff

Output

ID  |  Result
1   |  Surajit,Amit,Abhijit,Sourav,Asis