SRING_AGG and STUFF functions in MS SQL

Both STRING_AGG and STUFF are string functions in Microsoft SQL Server that are used to manipulate strings.

STRING_AGG is used to concatenate the values from multiple rows into a single string, separated by a specified delimiter. It was introduced in SQL Server 2017. The basic syntax is as follows:

STRING_AGG(expression, delimiter)

The expression is the column or expression that is being concatenated, and the delimiter is the separator between the values. Here’s an example:

SELECT STRING_AGG(Col1, ', ') as ConcatenatedValues
FROM MyTable

This will concatenate the values from the Col1 column, separated by a comma and a space.

If you want to sort the concatenated values in a specific order, you can use the ORDER BY clause in the STRING_AGG function. The ORDER BY clause is used to sort the values before they are concatenated.

Here’s an example:

SELECT STRING_AGG(Col1, ', ') WITHIN GROUP (ORDER BY Col1 ASC) as ConcatenatedValues
FROM MyTable

This will concatenate the values from the Col1 column, sorted in ascending order, and separated by a comma and a space.

In this example, the WITHIN GROUP clause is used to indicate that the ORDER BY clause applies to the concatenation operation. The ASC keyword is used to specify the sort order as ascending. If you want to sort in descending order, you can use the DESC keyword instead.

Note that the ORDER BY clause can only be used in the STRING_AGG function in SQL Server 2017 and later versions.

STUFF Function

STUFF is used to replace a portion of a string with another string. It is commonly used for concatenating strings or removing unwanted characters. The basic syntax is as follows:

STUFF(string_expression, start, length, replacement_string)

The string_expression is the string that you want to modify, and the start and length parameters define the portion of the string that you want to replace. The replacement_string is the new string that will replace the original portion. Here’s an example:

SELECT STUFF('Hello World', 7, 5, 'Stack')

This will replace the word “World” with “Stack” starting from the 7th position in the string. The result will be “Hello Stack”.

To concatenate all the values in a column into a single string separated by a delimiter, you can use the STUFF function with a subquery that selects the values to concatenate. Here’s an example:

SELECT STUFF(
    (SELECT ', ' + Col1
     FROM MyTable
     FOR XML PATH('')), 1, 2, '') as ConcatenatedValues

In this example, the subquery selects all the values from the Col1 column and concatenates them into a string separated by a comma and a space. The FOR XML PATH('') clause is used to concatenate the values in XML format. The STUFF function is then used to remove the first two characters (the comma and the space) from the concatenated string.

Note that the STUFF function is used in conjunction with the FOR XML PATH trick to concatenate values in SQL Server. This trick converts the rows into an XML document, which can then be manipulated using standard string functions like STUFF.