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).
LEFT(@tags,CHARINDEX(',', @tags + ',') - 1)
STRING SPLIT
now supports anenable_ordinal
parameter, according to this article, but I've not tried it myself.