3

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?

askolotl
  • 964
  • 1
  • 13
  • 27
  • This might be helpful https://stackoverflow.com/a/59749515/10532500 – Suraj Kumar Feb 08 '20 at 19:27
  • 3
    *"So how can I get exactly this result?"* by using a solution that provides ordinal positions; which `STRING_SPLIT` does not. It's lack of this information has been complained about a lot since it's introduction in SQL Server 2016, and MS seem to not want to add it to the function. I suggest looking up `DelimitedSplit8K_LEAD`, for example. – Thom A Feb 08 '20 at 19:32
  • 1
    `'a'` is "less than" `'b'`, @SurajKumar , that answer assumes that ordinal position and value order are the same. – Thom A Feb 08 '20 at 19:33

3 Answers3

9

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]
Zhorov
  • 28,486
  • 6
  • 27
  • 52
  • Thanks a lot, that's a great solution! Just to be complete: If the original string contains quotes, then they must be escaped? – askolotl Feb 09 '20 at 08:27
  • Wow thanks, is that great! Learned now there is an escaping function for specialized types like json! – askolotl Feb 09 '20 at 09:35
  • 1
    @askolotl Yes, but currently `STRING_ESCAPE()` supports only JSON. – Zhorov Feb 09 '20 at 09:38
  • What is it about this JSON approach that guarantees order? I don't believe it does. – wBob Feb 09 '20 at 14:48
  • 1
    @wBob It's in the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15#return-value) - _When you call OPENJSON with the default schema, ... the function returns a table with the following columns:a) Key. An nvarchar(4000) value that contains the name of the specified property or the index of the element in the specified array. The key column has a BIN2 collation, ..._. – Zhorov Feb 09 '20 at 17:33
  • 1
    This solution is great! – Pavel Nefyodov Apr 22 '21 at 21:34
2

If there are no duplicates in the string you are splitting you can use the below.

DECLARE @StringToSplit VARCHAR(100) = 'b,a,c';

SELECT  
    value 
FROM String_Split(@StringToSplit, ',') d
ORDER BY CHARINDEX(','+  value+ ',',  ',' + @StringToSplit + ',' )
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Interesting answer, but I have duplicates. – askolotl Feb 08 '20 at 18:51
  • 3
    @askolotl - you're out of luck then and should look for another string splitter than `String_Split` until it gets this missing functionality added – Martin Smith Feb 08 '20 at 18:52
  • @MartinSmith Yes, here the logic may be different for the ordering but order by clause works the same as it works on the table. – Suraj Kumar Feb 08 '20 at 18:54
  • 2
    @SurajKumar - Nobody is saying that `ORDER BY` can't be used with `String_Split` - the missing functionality is an index column saying the order that the split item was found. Ideally the function would return the same result set as `SELECT * FROM (VALUES ('b',1),('a',2),('c',3)) V(value, idx)` then we could order by `idx` – Martin Smith Feb 08 '20 at 18:59
  • 1
    @SurajKumar Exactly what Martin says. The split_string function is only returning the value, but it is not telling you in which position the string was found. There is an index column missing in the result. – SQL Police Feb 08 '20 at 19:04
  • 1
    If there are duplicates, you can use the method Ronen Ariely [describes here](http://ariely.info/Blog/tabid/83/EntryId/223/T-SQL-Playing-with-STRING_SPLIT-function.aspx). Please note, however, that he himself does not recommend this method due to performance issues. You might be better off using something other than the built in function. – Zohar Peled Feb 09 '20 at 09:38
-1
 <!-- language: lang-sql --> 
SELECT p.[Id], [p].[Body], [x].[value], x.[RN]
FROM [dbo].[Posts] AS [p]
CROSS APPLY (
SELECT ss.[value], 
     ROW_NUMBER() OVER (PARTITION BY [p].[Id] ORDER BY [p].[Id]) AS RN <--------- (SORT)
FROM STRING_SPLIT([p].[Body], ' ') AS [ss]
      ) AS x
Kirilll Evstigneev
  • 11
  • 2