SQL Server STUFF() Function With Syntax and Example

watch_later 06 February, 2019

Introduction


This article gives an explanation about STUFF() Function in SQL Server and shows you what is STUFF() Function in SQL Server, Use of STUFF() Function as well as when to use STUFF() Function in SQL Server.
SQL Server  STUFF() Function With Syntax and Example


Many of DBA/developers/programmers those 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 do, 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 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 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.

Syntex of STUFF() Function in SQL Server

STUFF(source_character_expresstion, start, length, character_expresstion)
If you analyzed 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 name as per our requirement.

To achieve this kind of requirement you just have to Get XML element string with FOR XML and then Remove leading comma with STUFF and finally Join on id to get the full list as I shown in 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, above result here, our string contains one comma and one white space(, ) in the starting index, so you have to Remove 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, are 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 while 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 look 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 shown in the example and as per our requirement output looks like as I Shown below.

Output

SQL Server  STUFF() Function With Syntax and Example

Summary


In SQL Server using 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.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

sentiment_satisfied Emoticon