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.comReal 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.pIDOutput:
