SQL Server STUFF() Function With Example

watch_later 7/08/2024
comment 1 Comment

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.

SQL Server  STUFF() Function With Syntax and Example

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

SQL Server  STUFF() Function With Syntax and Example

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.

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.

avatar

The Blog is really amazing. Keep blogging. We are with you.

Thanks
DirectDeals

delete September 22, 2022 at 4:45:00 PM GMT+5:30

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