SQL Server STUFF() Function With Syntax and Example

watch_later Wednesday, February 6, 2019
comment 1 Comment

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.

avatar
sai

I always enjoy reading quality articles by an individual who is obviously knowledgeable on their chosen subject. Ill be watching this post with much interest. Keep up the great work, I will be back

python training in chennai

delete February 10, 2019 at 10:33 PM



sentiment_satisfied Emoticon