Figure 1: SPLIT_AGG Usage #1 - Sort a Comma-Delimited String in Ascending Order
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.
STRING_AGG ( expression, separator ) [ <order_clause> ]
<order_clause> ::=
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
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 type depends on first argument (expression). If input argument is string type (`NVARCHAR`, `VARCHAR`), result type will be same as input type.
Here are useful sample usages of the `STRING_AGG` string function:
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 |
Figure 1: SPLIT_AGG Usage #1 - Sort a Comma-Delimited String in Ascending 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 |
Figure 2: SPLIT_AGG Usage #2 - Sort a Comma-Delimited String in Descending Order
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 |
Figure 3: SPLIT_AGG Usage #3 - Reverse a 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 |
Figure 4: SPLIT_AGG Usage #4 - Reverse a Comma-Delimited String
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 |
Figure 5: SPLIT_AGG Usage #5 - Sort a Comma-Delimited List of IPv4 Addresses