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]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)
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
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 (
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
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
0 comments:
Post a comment