Stuff in SQL Server

SQL Server Stuff is most commonly use inbuilt function in SQL Server.Stuff basically use for manipulate the string . According to MSDN stuff function insert a string into another string.SQL Stuff function takes 4 argument & the syntax of this function is given below
STUFF(Expression,Start,Length,ReplaceExpression)
In above argument has its own nature lets discuss about the these parameter first.
Expression: Its the source string and it has to be modify.
Start: Its the position from where given string have to delete & replcaeExpression have to insert.Its a integer value.If you pass negative value on it then stuff return null.
Length: Its a integer value its tell us how many number of character to delete from the Expression.
ReplaceExpression: This Replace the expression from the start to length character.
I also explain here xml path sql server example here with sql stuff function.All below query is tested in SQL Server 2008 R2

SQL Server Stuff Function Example (Insert String Into Another String at a Specific Location)

DECLARE @Expression VARCHAR(100)

DECLARE @ReplaceExpression VARCHAR(20)

SET @Expression = 'XYZ'

SET @ReplaceExpression = 'ABC-'

-- Add string at starting position

SELECT STUFF(@Expression,1, 0, @ReplaceExpression) AS [StuffExample]

--Replace First character and insert the new string

SELECT STUFF(@Expression,1, 1, @ReplaceExpression) AS [StuffExample]

--When we pass 0 at start position then

SELECT STUFF(@Expression,0, 1, @ReplaceExpression) AS [StuffExample]

--When we pass length greator than expression in second argument then (I used here len function)

SELECT STUFF(@Expression,LEN(@Expression)+1, 1, @ReplaceExpression) AS [StuffExample]

Stuff function output

SQL Server Stuff Example [Format Date & Time With Stuff]

With the help of stuff you can also format the date from MMDDYYYY to MM/DD/YYYY & time from HHMM to HH:MM

DECLARE @CollegeTime VARCHAR(10)

SET @CollegeTime = '0930'

SELECT STUFF(@CollegeTime, 3, 0, ':') AS [HH:MM]

DECLARE @ExamDate VARCHAR(20)

SET @ExamDate = '06102015'

SELECT STUFF(STUFF(@ExamDate, 3, 0, '/'), 6, 0, '/') AS [MM/DD/YYYY]

Stuff function Date Format output

HH:MM
09:30
MM/DD/YYYY
06/10/2015

Stuff Sql Server another example [Use for comma separated value]

Stuff function also used for get the comma separated value.To explain this i create a table and insert some data into it and use for xml path and stuff please have a look.
DECLARE @Employee TABLE (

    [EmployeeName] VARCHAR(100)

)

INSERT INTO @Employee(EmployeeName)

VALUES ('A'), ( 'B' ), ('C'), ('D')

SELECT STUFF((SELECT ',' + [EmployeeName]

                    FROM @Employee

              FOR  XML  PATH('')), 1, 1,'') AS  [Output As Comma Seperated value]

Output Example 3

Output As Comma Seperated value
A,B,C,D

Other use of Stuff

With the help of stuff we also mask the number for example i have a account number and i dont want to show the first few number of this so we we can use stuff like below

DECLARE @AccountNumber VARCHAR(25)

SET @AccountNumber = '9876543210568'

SELECT STUFF(@AccountNumber, 1, LEN(@AccountNumber) - 5,

REPLICATE('X', LEN(@AccountNumber) - 5)) AS [MaskAccountNumber]
If you execute just above query then output is
MaskAccountNumber
XXXXXXXX10568