Convert Csv string to a table and get the position

Question

The following code converts the CSV string to a table with its original order (the order is returned in the column Pos).

declare @input nvarchar(max) = 'x,y,z';

declare @x xml = '<s>' + replace(@input, ',', '</s><s>') + '</s>';

select  c.value('.', 'nvarchar(max)') Col, identity(int) Pos
into    #t
from    @x.nodes('/s') x ( c );

select * from #t;
drop table #t

returns

Col Pos
y   1
x   2
z   3

There are two problem

1. The Pos is not right, y got 1 and x got 2.
2. identity(int) requires select into

For input of 'z,y,x', z, y, and x should get 1, 2, and 3 respectively.

Answer 1

This is not guaranteed to be stable at all:

declare @input nvarchar(max) = 'x,y,z';
declare @x xml = '<s>' + replace(@input, ',', '</s><s>') + '</s>';

select  c.value('.', 'nvarchar(max)') Col, 
        ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS pos
from    @x.nodes('/s') x ( c )
OPTION (MAXDOP 1);

DBFiddle Demo

1. Disable parallelism
2. Identity(int) -> ROW_NUMBER

---

Modified split function:

CREATE FUNCTION dbo.Split(@origString varchar(max), @Delimiter char(1))     
returns @temptable TABLE (pos INT IDENTITY(1,1), items varchar(max))     
as     
begin  


declare @idx int     
declare @split varchar(max)     

select @idx = 1     
    if len(@origString )<1 or @origString is null  return     

while @idx!= 0     
begin     
    set @idx = charindex(@Delimiter,@origString)     
    if @idx!=0     
        set @split= left(@origString,@idx - 1)     
    else     
        set @split= @origString

    if(len(@split)>0)
        insert into @temptable(Items) values(@split)     

    set @origString= right(@origString,len(@origString) - @idx)     
    if len(@origString) = 0 break     
end 
return     
end

Call:

SELECT *
FROM dbo.Split('z,y,x', ',');

DBFiddle Demo2