SQL Server: STUFF() Function

In SQL Server (Transact-SQL), the 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

The syntax for the STUFF function in SQL Server (Transact-SQL) is:

STUFF( source_string, start, length, add_string )

SELECT STUFF('techtalk.com', 1, 8, 'techfura');
Output: techfura.com

Real time scenario; Lets say table called ‘Parent’ contains the parent info, and another table ‘Dependent’ contains list of dependents for the parent listed in Parent table.

Table – ‘Parent’

+----+------+
| PId | Name |
+----+------+    
| 1  | Alex |
| 2  | Mark |
| 3  | Jack |
| 4  | Ruth |
| 5  | Samri|
+----+------+

Table – ‘Dependent’

+----+------------+
| DId |PId  | Name |
+----+-------------+    
| 1  |1 |Linda  |
| 2  | 1|Bob    |
| 3  | 3|Sami   |
| 4  | 4|Daniel |
| 5  | 4|Liou   |
| 6  | 5|Euael  |
| 7  | 3|Get    |
| 8  | 2|Mekdy  |
| 9  | 4|Dave   |
+----+----------+

The following query using stuff() will return the required results:

SELECT  DISTINCT pr.pID, pr.name AS Parent, 
(SELECT STUFF((SELECT DISTINCT ', ' + dr.name 
 FROM (SELECT dr.name 
								from Dependent dr 
								WHERE dr.PID = pr.pID)dr 
 FOR XML PATH (''), TYPE).value('.','varchar(max)'), 1, 2, '' )) AS Dependents
FROM Parent pr
INNER JOIN Dependent dt  ON  dt.pID = pr.pID

Output: