The Stuff Function in SQL Server is used to deletes a sequence of characters from a source string and then inserts a string into another string.
Character_Expression: String on which you want to insert New_String using the SQL Server stuff function.
Starting_Position: From which index position, you want to start inserting the New_String characters.
Length: How many characters you want to delete from the Character_Expression. SQL Stuff Function will start at Starting_Position and delete the specified number of characters from the Character_Expression
New_String: New string you want to stuff inside the Character_Expression at Starting_Position
Syntax: SELECT STUFF (Character_Expression, Starting_Position, Length, New_String)
FROM [Source]
DECLARE @Character_Expression varchar(50)
SET @Character_Expression = 'Learn Server'
-- Starting Position = 6 and End Position = 0
SELECT STUFF (@Character_Expression, 6, 0, ' SQL') AS 'SQL STUFF'
-- Starting Position = 7 and End Position = 6
SELECT STUFF (@Character_Expression, 7, 6, 'SQL') AS 'SQL STUFF'
-- Starting Position = 1 and End Position = 5
SELECT STUFF (@Character_Expression, 1, 5, '') AS 'SQL STUFF'
-- Starting Position = 20 and End Position = 5
SELECT STUFF (@Character_Expression, 20, 5, 'SQL ') AS 'SQL STUFF'
One more use of STUFF to get comma separated values in a a rows as per given below example.
DECLARE @TblCity TABLE(CountryCode INT, City VARCHAR(50))
INSERT @TblCity(CountryCode, City)
VALUES
(1, 'Johannesburg'),
(1, 'Cape Town'), --South Africa
(2, 'New York'),
(2, 'Washington'), --USA
(3, 'Paris'),
(3, 'Nice'), --France
(4, 'Rome'),
(4, 'Bologna'), --Itlay
(5, 'Athens'),
(5, 'Volos') --Greece
---// Add the Following SELECT FOR XML PATH Query to concatenate the Cities belonging to the same country code onto one line:
--Concat
SELECT DISTINCT CountryCode,
STUFF((SELECT ',' + City
FROM @TblCity t1
WHERE t1.CountryCode = t2.CountryCode
FOR XML PATH('')), 1, 1,'')
FROM @TblCity t2
Below is also an example to make comma separated values in a column.
But this STRING_AGG() function can be used in and above versions of SQL Server 2017
STRING_AGG is an aggregate function that takes all expressions from rows and concatenates them into a single string. Expression values are implicitly converted to string types and then concatenated. The implicit conversion to strings follows the existing rules for data type conversions.
SELECT CountryCode, City = STRING_AGG(City, ', ') WITHIN GROUP (ORDER BY CountryCode)
FROM @TblCity
GROUP BY CountryCode
As compare to FOR XML PATH query, STRING_AGG() function is much faster.. detail comparison given in below link.
No comments:
Post a Comment