STRING_AGG String Function¶
The STRING_AGG string function concatenates the values of string expressions and places separator values between them. The separator isn't added at the end of string.
Syntax¶
STRING_AGG ( expression, separator ) [ <order_clause> ]
<order_clause> ::=
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
Arguments¶
expression
Is an expression of any type. Expressions are converted to NVARCHAR or VARCHAR types during concatenation. Non-string types are converted to NVARCHAR type.
separator
Is an expression of NVARCHAR or VARCHAR type that is used as separator for concatenated strings. It can be literal or variable.
<order_clause>
Optionally specify order of concatenated results using WITHIN GROUP clause:
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
<order_by_expression_list>
A list of non-constant expressions that can be used for sorting results. Only one order_by_expression is allowed per query. The default sort order is ascending.
Return Types¶
Return type depends on first argument (expression). If input argument is string type (NVARCHAR, VARCHAR), result type will be same as input type.s
Useful Sample Usages of the STRING_AGG String Function¶
Here are useful sample usages of the STRING_AGG string function:
Usage #1: Sort a Comma-Delimited String in Ascending Order¶
A comma-delimited string of values can be sorted with the use of both the STRING_SPLIT table-valued string function and the STRING_AGG string function.
The STRING_SPLIT table-valued string function splits a string into rows of substrings, based on a specified separator character.
Given a string value of one,two,three,four,five,six,seven,eight,nine,ten, the STRING_SPLIT function will convert this into a row of substrings:
DECLARE @Input VARCHAR(1000)
SET @Input = 'one,two,three,four,five,six,seven,eight,nine,ten';
SELECT value
FROM STRING_SPLIT(@Input, ',');
| value |
|---|
| one |
| two |
| three |
| four |
| five |
| six |
| seven |
| eight |
| nine |
| ten |
To concatenate these rows of substrings back into a single value, the STRING_AGG string function can be used. Since we want to return the rows of substrings in ascending order, the WITHIN GROUP (ORDER BY) clause will be included with the STRING_AGG function. Here's how the query will look like to return the original string in alphabetical order:
DECLARE @Input VARCHAR(1000)
SET @Input = 'one,two,three,four,five,six,seven,eight,nine,ten';
SELECT STRING_AGG(value, ',') WITHIN GROUP (ORDER BY value ASC) AS [Output]
FROM STRING_SPLIT(@Input, ',');
| Output |
|---|
| eight,five,four,nine,one,seven,six,ten,three,two |
Usage #2: Sort a Comma-Delimited String in Descending Order¶
To return the comma-delimited string of values in descending order in the previous example, the DESC order clause will be used in the WITHIN GROUP (ORDER BY) instead of the ASC clause.
DECLARE @Input VARCHAR(1000)
SET @Input = 'one,two,three,four,five,six,seven,eight,nine,ten';
SELECT STRING_AGG(value, ',') WITHIN GROUP (ORDER BY value DESC) AS [Output]
FROM STRING_SPLIT(@Input, ',');
| Output |
|---|
| two,three,ten,six,seven,one,nine,four,five,eight |
Usage #3: Reverse a String¶
The REVERSE string function, a built-in function in SQL Server Transact-SQL, returns the reverse of a string value.
SELECT REVERSE('ABDEFGHIJKLMNOPQRSTUVWXYZ') AS [Output];
| Output |
|---|
| ZYXWVUTSRQPONMLKJIHGFEDBA |
This function can be simulated with the use of the STRING_AGG and with a little help from the GENERATE_SERIES function. The GENERATE_SERIES function generates a series of numbers within a given interval. The interval and the step between series values are defined by the user. As a simple example, here's how generate a row of numbers from 1 to 5 in ascending order:
SELECT value
FROM GENERATE_SERIES(1, 5);
| value |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
If the first parameter passed to the GENERATE_SERIES function is larger than the second parameter, it will automatically do a descending series of numbers:
SELECT value
FROM GENERATE_SERIES(5, 1);
| value |
|---|
| 5 |
| 4 |
| 3 |
| 2 |
| 1 |
To reverse the string ABCDEFGHIJKLMNOPQRSTUVWXYZ without using the REVERSE string function, the query will look as follows:
DECLARE @Input VARCHAR(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
SELECT STRING_AGG(SUBSTRING(@Input, value, 1), '') AS [Output]
FROM GENERATE_SERIES(LEN(@Input), 1);
| Output |
|---|
| ZYXWVUTSRQPONMLKJIHGFEDCBA |
Usage #4: Reverse a Comma-Delimited String¶
Similar to the first and second usage examples described above wherein a comma-delimited string of values were sorted by the string value in ascending and descending order, respectively, the comma-delimited string of values can also be sorted in reverse order of their position in the string. This can be accomplished with the use of the third parameter of the STRING_SPLIT function. The third parameter of the STRING_SPLIT function, which is only available in SQL Server 2022 and later versions, accepts an int or bit expression that serves as a flag to enable or disable the ordinal output column. A value of 1 passed to the third parameter of the STRING_SPLIT function enables the ordinal column. If the third parameter is omitted, NULL, or has a value of 0, the ordinal column is disabled.
To illustrate, given an input string of one,two,three,four,five,six,seven,eight,nine,ten, this can be split into individual values as rows together with the ordinal position of the string value within the full string:
DECLARE @Input VARCHAR(1000)
SET @Input = 'one,two,three,four,five,six,seven,eight,nine,ten';
SELECT *
FROM STRING_SPLIT(@Input, ',', 1);
| value | ordinal |
|---|---|
| one | 1 |
| two | 2 |
| three | 3 |
| four | 4 |
| five | 5 |
| six | 6 |
| seven | 7 |
| eight | 8 |
| nine | 9 |
| ten | 10 |
Displaying the results in descending order can be accomplished by simply adding an ORDER BY ordinal DESC clause to the query:
DECLARE @Input VARCHAR(1000)
SET @Input = 'one,two,three,four,five,six,seven,eight,nine,ten';
SELECT *
FROM STRING_SPLIT(@Input, ',', 1)
ORDER BY ordinal DESC;
| value | ordinal |
|---|---|
| ten | 10 |
| nine | 9 |
| eight | 8 |
| seven | 7 |
| six | 6 |
| five | 5 |
| four | 4 |
| three | 3 |
| two | 2 |
| one | 1 |
Now adding the STRING_AGG string function to put the different values back together, here's how the query may look like:
DECLARE @Input VARCHAR(1000)
SET @Input = 'one,two,three,four,five,six,seven,eight,nine,ten';
SELECT STRING_AGG(value, ',') AS [Output]
FROM STRING_SPLIT(@Input, ',', 1)
ORDER BY ordinal DESC;
But running this query generates the following error:
Query 1 ERROR at Line 3: : Msg: 8127, Line 7, State: 1, Level: 16
Column "STRING_SPLIT_56D4EB33-B76F-4E5F-AE76-E43519C5E445.ordinal" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
To overcome this error, instead of adding the ORDER BY ordinal DESC in the main query, it can be added as part of the WITHIN GROUP clause of the STRING_AGG string function, as follows:
DECLARE @Input VARCHAR(1000)
SET @Input = 'one,two,three,four,five,six,seven,eight,nine,ten';
SELECT STRING_AGG(value, ',') WITHIN GROUP (ORDER BY ordinal DESC) AS [Output]
FROM STRING_SPLIT(@Input, ',', 1);
| Output |
|---|
| ten,nine,eight,seven,six,five,four,three,two,one |
Usage #5: Sort a Comma-Delimited List of IPv4 Addresses¶
Given a comma-delimited list of IPv4 addresses, the list can be sorted using the STRING_SPLIT string function to separate each IP:
DECLARE @Input VARCHAR(1000)
SET @Input = '10.0.0.1,192.120.40.243,207.46.199.60,255.255.0.0,255.255.255.255,64.233.188.15,98.123.251.21';
SELECT value AS [IP Address]
FROM STRING_SPLIT(@Input, ',')
ORDER BY value;
| IP Address |
|---|
| 10.0.0.1 |
| 192.120.40.243 |
| 207.46.199.60 |
| 255.255.0.0 |
| 255.255.255.255 |
| 64.233.188.15 |
| 98.123.251.21 |
As can be seen from the result, the list of IP address is sorted by its string value instead of the numeric value of each IP address component. IP address 255.255.255.255 should be returned last while IP address 64.233.188.15 should be the second from the top in the result instead of second from the bottom. One trick that can be used to split the IP address into its individual components is with the use of the PARSENAME string function.
The PARSENAME metadata function returns the specified part of an object name. The parts of an object that can be retrieved are the object name, owner name, database name and server name. The PARSENAME function does not indicate whether an object by the specified name exists. PARSENAME just returns the specified part of the specified object name.
PARSENAME ( 'object_name' , object_part )
The object_name parameter is the name of the object for which to retrieve the specified object part. The object_part parameter, which is of int data type, is the object part to return and can have a value of 1 for the object name, 2 for the schema name, 3 for the database name and 4 for the server name.
DECLARE @ObjectName SYSNAME
SET @ObjectName = 'MyServer.SQLServerHelper.dbo.Customer'
SELECT PARSENAME(@ObjectName, 1) AS [ObjectName],
PARSENAME(@ObjectName, 2) AS [SchemaName],
PARSENAME(@ObjectName, 3) AS [DatabaseName],
PARSENAME(@ObjectName, 4) AS [ServerName]
| ObjectName | SchemaName | DatabaseName | ServerName |
|---|---|---|---|
| Customer | dbo | SQLServerHelper | MyServer |
Similar to a fully qualified object name, an IP address is made of 4 parts separated by a period. Using the PARSENAME metadata function, the 4 parts of an IP address can easily be extracted then converted to an int data type and be used for sorting. Here's how the query will look like to sort a list of IP addresses:
DECLARE @Input VARCHAR(1000)
SET @Input = '10.0.0.1,192.120.40.243,207.46.199.60,255.255.0.0,255.255.255.255,64.233.188.15,98.123.251.21';
SELECT value AS [IP Address]
FROM STRING_SPLIT(@Input, ',')
ORDER BY CAST(PARSENAME([value], 4) AS INT),
CAST(PARSENAME([value], 3) AS INT),
CAST(PARSENAME([value], 2) AS INT),
CAST(PARSENAME([value], 1) AS INT)
| IP Address |
|---|
| 10.0.0.1 |
| 64.233.188.15 |
| 98.123.251.21 |
| 192.120.40.243 |
| 207.46.199.60 |
| 255.255.0.0 |
| 255.255.255.255 |
To put the result back together into a single concatenated string of IP addresses, the STRING_AGG string function will be used. But simply adding the STRING_AGG string function generates an error:
DECLARE @Input VARCHAR(1000)
SET @Input = '10.0.0.1,192.120.40.243,207.46.199.60,255.255.0.0,255.255.255.255,64.233.188.15,98.123.251.21';
SELECT STRING_AGG(value, ',')
FROM STRING_SPLIT(@Input, ',')
ORDER BY CAST(PARSENAME([value], 4) AS INT),
CAST(PARSENAME([value], 3) AS INT),
CAST(PARSENAME([value], 2) AS INT),
CAST(PARSENAME([value], 1) AS INT);
Query 1 ERROR at Line 3: : Msg: 8127, Line 7, State: 1, Level: 16
Column "STRING_SPLIT_CB47892C-14E4-4267-8D3A-129888DB2D2F.value" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
To solve this issue, the ORDER BY clause needs to be added as part of the WITHIN GROUP clause of the STRING_AGG string function. Here's how the query will look like:
DECLARE @Input VARCHAR(1000)
SET @Input = '10.0.0.1,192.120.40.243,207.46.199.60,255.255.0.0,255.255.255.255,64.233.188.15,98.123.251.21';
SELECT STRING_AGG(value, ',')
WITHIN GROUP (ORDER BY CAST(PARSENAME([value], 4) AS INT),
CAST(PARSENAME([value], 3) AS INT),
CAST(PARSENAME([value], 2) AS INT),
CAST(PARSENAME([value], 1) AS INT)) AS [Output]
FROM STRING_SPLIT(@Input, ',');
| Output |
|---|
| 10.0.0.1,64.233.188.15,98.123.251.21,192.120.40.243,207.46.199.60,255.255.0.0,255.255.255.255 |
Related Articles¶
- STRING_SPLIT Sample Uses of the STRING_SPLIT String Function
- PARSENAME Samples Uses of the PARSENAME Metadata Function