STRING_SPLIT and ordered results

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, ',');

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).

StackExchange Database Administrators Q#207274, answer score: 17