Introduction
This article gives an explanation of the STUFF() Function in SQL Server and shows you what is STUFF() Function in SQL Server, the Use of STUFF() Function as well as when to use the STUFF() Function in SQL Server.
In my previous articles, I explained Cursor In SQL Server with Syntax and Example and SQL Server Performance Tuning Techniques as well as How to Fix The 'Microsoft.ACE.OLEDB.12.0' The provider Is Not Registered On The Local Machine Error and also explained How Fix SQL Server Database Restore Error.
Many DBA/developers/programmers who work with Microsoft SQL Server will have at least heard talk about the STUFF() Function. Even if any DBA/developers/programmers know on a basic level what STUFF() Function does, they are not always certain when to use STUFF() Function and how to write the code to use STUFF(). So, here I will show you how you can use the STUFF() function to archive your requirement/needs with its syntax and a simple example.
What is STUFF() Function and Use of it in SQL Server?
In SQL Server STUFF() function is used to delete a specified sequence of characters from a source/Input string and then inserts another set of sequence of characters at a specified starting point.
Syntex of STUFF() Function in SQL Server
STUFF(source_character_expresstion, start, length, character_expresstion)
If you analyzed the given syntax then STUFF() requires 4 arguments that are explained below.
Arguments or Parameters Values
source_character_expresstion:- The input/source string to be modified and this is the required parameter.
start:- The position in the input/source to start to delete some specified sequence of characters.
length:- The number of characters to delete from input/source string
character_expresstion:- The new string to insert into the input/source string at the start position
Requirement
1) Create a sample table with the column DepartmentId, DepartmentName, and EmployeeName.
2) Insert some dummy records on the table for demonstration.
3) List all the Employee Department Wise Using STUFF() Function (i.g Employee AA, BB, KK Work in 1st Department, Employee CC, DD Work in 2nd Department, Employee MM, NN, OO Work in 3rd Department and etc. )
Implementation
Create Table
DECLARE @TableT AS TABLE ( DepartmentId INT , DepartmentName NVARCHAR(50) , EmployeeName NVARCHAR(50) )
Insert Records in Table
INSERT INTO @TableT ( DepartmentId, DepartmentName, EmployeeName) VALUES ( 1, N'.NET', 'NikunjSatasiya'), ( 1, N'.NET', 'HirenDobariya'), ( 2, N'ANDROID', 'SrutiPatel'), ( 3, N'PHP', 'Krish'), ( 3, N'PHP', 'PriyaPatel'), ( 3, N'PHP', 'RadhaSavaliya')
List all the Employee Department Wise Using STUFF() Function
SELECT DepartmentId, DepartmentName, EmployeeName = STUFF((SELECT ', ' + EmployeeName FROM @TableT t1 WHERE t1.DepartmentId = t2.DepartmentId FOR XML PATH ('')) , 1, 2, '') from @TableT t2 GROUP BY DepartmentId, DepartmentName;
Explanation
If you analyzed the above example then I have generated department-wise comma (,) separated employee names as per our requirement.
To achieve this kind of requirement you just have to Get the XML element string with FOR XML and then Remove the leading comma with STUFF and finally Join on id to get the full list as I shown in the above example where you can add FOR XML PATH to the end of your SQL query and that will allow you to output the results of the query as XML elements, with the name of elements contained in the PATH argument. I.g If you run the following statement.
SELECT ', ' + EmployeeName AS EmployeeName FROM @TableT t1 FOR XML PATH ('')
By passing in a blank string (FOR XML PATH('')), you will get the following result
, NikunjSatasiya, HirenDobariya, SrutiPatel, Krish, PriyaPatel, RadhaSavaliya
Look, at the above result here, our string contains one comma and one white space(, ) in the starting index, so you have to remove the leading comma and white space with STUFF, and as I said In SQL server the STUFF statement literally "stuffs” one input/source string into another input/source string, replacing characters within the first input/source string. So, Here I, however, am using it simply to remove the first character of the resultant list of values.
SELECT STUFF(( SELECT ', ' + EmployeeName AS EmployeeName FROM @TableT t1 FOR XML PATH ('')), 1, 2, '') FROM @TableT
where our parameters are something like the string to be “stuffed” is the full list of EmployeeName with a leading comma and white space, The Index/location to start deleting and inserting characters is 1 where I stuffed into a blank string and at last specified the number of characters to delete from my source string and finally, the string looks like as shown below.
NikunjSatasiya, HirenDobariya, SrutiPatel, Krish, PriyaPatel, RadhaSavaliya
Finally, I Joined it on DepartmentId to get the department-wise full list of Employee names as I showed in the example and as per our requirement output looks like as I Showed below.
Output
Summary
In SQL Server using the STUFF() function, you can deletes a specified sequence of characters from a source/Input string and then inserts another set of sequence of characters at a specified starting point. I hope this article helps you to archive your requirement.
