What is Stuff Function? Stuff function with XML Path

The staff function is a string function, the main purpose of this function to use to replay the string into another string. Let have look at the below example.

For example, the actual string value is jprabhakaran@gmail.com , now the requirement we need to gmail.com to yahoo.com.

Email	NewEmailid
jprabhakaranmca@gmail.com	jprabhakaranmca@yahoo.com

Syntax:

STUFF ( character_expression , start , length , replaceWith_expression )

You can see here, the starting position 17 and End position 25 for @gamil.com string and we can replace the gmail.com to yahoo.com.

Let we will see how we can use Stuff function in XML Path Clause. We will discuss detailly about XML Path Clause separately in separate article.

Have look on the below input and output result, here we need to get comma separately based on gender.

Create table and insert data by using the below script

USE [schooldb]
GO
/****** Object:  Table [dbo].[student]    Script Date: 8/12/2021 6:50:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NOT NULL,
[gender] [varchar](50) NOT NULL,
[DOB] [datetime] NOT NULL,
[total_score] [int] NOT NULL,
[mobile] [bigint] NOT NULL,
 CONSTRAINT [PK__student__3213E83FBD07AA07] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[student] ON
GO
INSERT [dbo].[student] ([id], [name], [gender], [DOB], [total_score], [mobile]) VALUES (1, N'Jolly', N'Female', CAST(N'1989-06-12T00:00:00.000' AS DateTime), 500, 3434343)
GO
INSERT [dbo].[student] ([id], [name], [gender], [DOB], [total_score], [mobile]) VALUES (2, N'Jon', N'Male', CAST(N'1974-02-02T00:00:00.000' AS DateTime), 545, 9738382759)
GO
INSERT [dbo].[student] ([id], [name], [gender], [DOB], [total_score], [mobile]) VALUES (3, N'Sara', N'Female', CAST(N'1988-03-07T00:00:00.000' AS DateTime), 600, 9738382757)
GO
INSERT [dbo].[student] ([id], [name], [gender], [DOB], [total_score], [mobile]) VALUES (4, N'Laura', N'Female', CAST(N'1981-12-22T00:00:00.000' AS DateTime), 400, 863838275974)
GO
INSERT [dbo].[student] ([id], [name], [gender], [DOB], [total_score], [mobile]) VALUES (5, N'Alan', N'Male', CAST(N'1993-07-29T00:00:00.000' AS DateTime), 500, 9738382750)
GO
INSERT [dbo].[student] ([id], [name], [gender], [DOB], [total_score], [mobile]) VALUES (6, N'Kate', N'Female', CAST(N'1985-01-03T00:00:00.000' AS DateTime), 500, 738382759)
GO
INSERT [dbo].[student] ([id], [name], [gender], [DOB], [total_score], [mobile]) VALUES (7, N'Joseph', N'Male', CAST(N'1982-04-09T00:00:00.000' AS DateTime), 643, 9738382759)
GO
INSERT [dbo].[student] ([id], [name], [gender], [DOB], [total_score], [mobile]) VALUES (8, N'Mice', N'Male', CAST(N'1974-08-16T00:00:00.000' AS DateTime), 543, 9738382740)
GO
INSERT [dbo].[student] ([id], [name], [gender], [DOB], [total_score], [mobile]) VALUES (9, N'Wise', N'Male', CAST(N'1987-11-11T00:00:00.000' AS DateTime), 499, 9738382444)
GO
INSERT [dbo].[student] ([id], [name], [gender], [DOB], [total_score], [mobile]) VALUES (10, N'Elis', N'Female', CAST(N'1990-10-28T00:00:00.000' AS DateTime), 400, 9738355555)
GO
SET IDENTITY_INSERT [dbo].[student] OFF
GO

Expected output below..!

By using the below query we can get the above about.

 SELECT DISTINCT gender,
(
SELECT STUFF(
(Select ',' + name from student b Where b.gender = a.gender for xml path(''))
,1,1,'')
) as [Names]
From student a 

Thanks for your time look on this topic..!