STRING_SPLIT and ordered results

Question

Can you assume that the result of the string_split function returns elements in order? In short, is the following code correct?

DECLARE @tags NVARCHAR(400) = 'clothing,road,touring,bike' 
DECLARE @FIRST nvarchar(100);

SELECT TOP 1 @FIRST = value 
FROM STRING_SPLIT(@tags, ',');  

Answer 1

TOP 1 without ORDER BY could not be guaranteed to return the same results even if the output of STRING_SPLIT() were guaranteed (which it most definitely is not). While in real-world usage you may find it hard to come up with a counter-example where the data is not returned in order, this is a terrible kind of thing to do. Trusting that something always works because you've never seen it break is like assuming that if you put a deer sign on the highway that is the only place you'll ever see a deer.

However, let's look at another way we could solve this. Why don't we locate the position of each individual string in the overall list:

DECLARE 
  @tags nvarchar(400) = N'clothing,road,touring,bike',
  @c nchar(1) = N',';

SELECT value, CHARINDEX(@c + value + @c, @c + @tags + @c) 
  -- we surround the value and the string with leading/trailing ,
  -- so that cloth isn't a false positive for clothing
  FROM STRING_SPLIT(@tags, ',') AS t;

Output here is:

Do you think we could use this to determine the first element in the list? Of course! Let's try again:

DECLARE 
  @tags nvarchar(400) = N'clothing,road,touring,bike',
  @first nvarchar(100),
  @c nchar(1) = N',';

;WITH t AS
(
  SELECT value, idx = CHARINDEX(@c + value + @c, @c + @tags + @c) 
  FROM STRING_SPLIT(@tags, ',')
)
SELECT TOP (1) @first = value FROM t ORDER BY idx;

PRINT @first;

Output:

clothing

You could use the same logic to find the last element, just change the ORDER BY idx to ORDER BY idx DESC. In fact, you could use this logic to return the nth string in the list:

DECLARE 
  @tags nvarchar(400) = N'clothing,road,touring,bike',
  @c nchar(1) = N',',
  @nth tinyint;

SET @nth = 3;

;WITH t AS
(
  SELECT value, idx = ROW_NUMBER() OVER 
    (ORDER BY CHARINDEX(@c + value + @c, @c + @tags + @c))
  FROM STRING_SPLIT(@tags, ',')
)
SELECT * FROM t WHERE idx = @nth;

Result:

touring

As a disclaimer, if you have duplicates, that's going to mess things up, because the index value will always represent the first appearance of that value in the string. You can switch from ROW_NUMBER() to DENSE_RANK() but that won't solve for all cases. You can de-dupe the string first (I talk about some edge cases here).

Answer 2

You definitely make a cheating like below:

SELECT  value,
        row_number() OVER (ORDER BY current_timestamp) AS row
FROM    String_Split('your,string,here',',') d

Answer 3

You can use an identity and a temporary table, something like this..

I think it's cleaner:

select  'OrderNum' = identity(int,1,1), * 
into    #tmo
from    STRING_SPLIT ('kjds|kdkjsdhkjshdkj3hjk|3hg43ui46g33754g6325', '|')

select * from #tmo

So, you can obtain this result

|OrderNum    |value
|----------- |----------------------
|1           |kjds
|2           |kdkjsdhkjshdkj3hjk
|3           |3hg43ui46g33754g6325

BUT, microsoft says, the output order of String_SPLIT function is not guaranteed

you can obtain the real order with the "ordinal" parameter, but only works for SQL 2022 and forward

Answer 4

this is my variant with guaranteed right sorting:

DECLARE @string NVARCHAR(256) = N'   a    b x  b  c';
-- creating table with unique identity, for sorting later:
DECLARE @dict TABLE (
    id          INT IDENTITY(1,1),
    value       NVARCHAR(256)
);
DECLARE @result NVARCHAR(256)  = '';
-- split string on parts and insert rows on @dict:
INSERT @dict SELECT value FROM STRING_SPLIT(@string, ' ') WHERE value <> '';
-- got sorted result:
SELECT value FROM @dict ORDER BY id;

Done.

p.s. if you`re wondering, this my function for getting sorted words (like single line):

CREATE FUNCTION [dbo].[getWordsFromString] (@string NVARCHAR(256), @offset TINYINT, @limit TINYINT)
RETURNS NVARCHAR(256)
WITH SCHEMABINDING
AS
BEGIN
    -- creating table with unique identity, which will be used for sorting below
    DECLARE @dict TABLE (
        id          INT IDENTITY(1,1),
        value       NVARCHAR(256)
    );
    DECLARE @result NVARCHAR(256)  = '';
    IF @offset IS null
        SET @offset = 0;
    IF @limit = 0 OR @limit IS null
        SET @limit = 1;
    -- split string into raws and insert them into temp table
    INSERT @dict SELECT value FROM STRING_SPLIT(@string, ' ') WHERE value <> '';
    -- aggregate rows into one row splitted by spaces
    SELECT @result = COALESCE(@result + ' ', '') + value
    FROM @dict
    ORDER BY id-- сортируем по позиции слова в строке
    OFFSET @offset ROWS
    FETCH NEXT @limit ROWS ONLY;
    RETURN LTRIM(@result);
END

Example usage:

SELECT dbo.getWordsFromString(N'   a    b x  b  c', 1, 2);

Result:

b x