Now I found this great string_split() function, and then I learned that the output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string.. This renders the function useless for me.
Is there maybe a workaround?
EDIT - Example:
SELECT value FROM String_Split('b,a,c', ',')
In this case I want this result in this order:
value
------
b
a
c
However, according to Microsoft, this order is not guaranteed. So how can I get exactly this result?
Original answer:
You may try to use an approach, based on JSON. You need to transform the input string into a valid JSON array (b,a,c is transformed into ["b","a","c"]) and then parse this array with OPENJSON() and default schema. The result is a table with columns key, value and type, and based on the documentation, the key column is an nvarchar(4000) value that contains the name of the specified property or the index of the element in the specified array.
Statement:
DECLARE @StringToSplit VARCHAR(100) = 'b,a,c';
SELECT [value]
FROM OPENJSON(CONCAT('["', REPLACE(@StringToSplit, ',', '","'), '"]'))
ORDER BY CONVERT(int, [key])
Result:
value
b
a
c
If you have quotes in the input string, try with the following statement, using STRING_ESCAPE():
DECLARE @StringToSplit VARCHAR(100) = '"b",''a'',c';
SELECT [value]
FROM OPENJSON(CONCAT('["', REPLACE(STRING_ESCAPE(@StringToSplit, 'json'), ',', '","'), '"]'))
ORDER BY CONVERT(int, [key])
Result:
value
"b"
'a'
c
Update:
Starting from SQL Server 2022, the STRING_SPLIT() function supports an optional third parameter (enable_ordinal). The parameter is an int or bit expression that serves as a flag to enable or disable the ordinal output column. A value of 1 enables the ordinal column. If enable_ordinal is omitted, NULL, or has a value of 0, the ordinal column is disabled.
SELECT [value]
FROM STRING_SPLIT(@StringToSplit, ',', 1)
ORDER BY [ordinal]