-- 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?

IF OBJECT_ID('dbo.ufn_Split',N'FN') IS NOT NULL DROP FUNCTION dbo.ufn_Split;
GO

CREATE FUNCTION dbo.ufn_Split( @str nvarchar(max), @d nvarchar(5) )
RETURNS nvarchar(max)
--WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
    DECLARE @pos int = 1, @lpos int = 1, @id int = 0, @list nvarchar(max)
    WHILE LEN(@str) >= @pos BEGIN
        SET @id = @id+1; SET @pos = CHARINDEX(@d,@str,@lpos);
        IF @pos = 0 SET @pos = LEN(@str)+1;
        SET @List = SUBSTRING(@str,@lpos,@pos-@lpos);
        SET @lpos = @pos+1;
    END
    RETURN @list
END
GO