The STUFF function combines two strings into one. It deletes a specified number of characters from the beginning of the first string, then inserts the second string into the beginning of the first string.
A null string is returned if the start position or length is negative, or if the starting position is greater than the length of the first string. A null value is returned if the start position is 0. If the length of the string to be deleted exceeds the length of the first string, it is cut off at the first character of the first string.
If the returned value is more than the maximum supported by the return type, an error is raised.
The samples below will help you understand more clearly.
Examples:
SELECT STUFF(‘SmallData N SQL’, 1, 5, ‘Big’) Output;
The preceding command will help to replace the word “small” with “big,” resulting in “BigData N SQL.”
SELECT STUFF(‘SQL Materials’, 1 , 3, ‘SQL Server’) Output;
The preceding command will help to replace the word “SQL” with “SQL Server” resulting in “SQL Server Materials”
SELECT STUFF(‘1145’, 3, 0, ‘:’) AS ActualTime;
The above statement will help in splitting the value thats in integer format into actual Time.
SELECT DISTINCT Dname,
STUFF((SELECT ‘,’+ a.EName FROM Emp a
WHERE a.DeptNo = b.DeptNo
FOR XML PATH(”)),1,1,”) AS Employees
FROM Dept b
The preceding statement will aid in the grouping of departments and all of their employees into a single row (concatenation).
I hope you found this post to be interesting and useful.
Please join our mailing list to receive more interesting information.
Big Data and SQL 
4 comments