-- splits a string by a delimiter string and returns each part in a table with a 1-based index in order of occurrence.
-- it should be deterministic, no?

CREATE FUNCTION dbo.ufn_Split( @str nvarchar(max), @d varchar(5) )
RETURNS @List TABLE (id int PRIMARY KEY, item nvarchar(max))
AS
BEGIN
    DECLARE @pos int = 1, @lpos int = 1, @id int = 0
    WHILE LEN(@str) >= @pos BEGIN
        SET @id = @id+1; SET @pos = CHARINDEX(@d,@str,@lpos);
        IF @pos = 0 SET @pos = LEN(@str)+1;
        INSERT INTO @List SELECT @id AS id, SUBSTRING(@str,@lpos,@pos-@lpos);
        SET @lpos = @pos+1;
    END
    RETURN
END