The second version has also taught me a lot.
I have been trying to complete the function so that it would return as separate "words" the characters between each delimiter (a comma).
I know that there is an excellent string splitter function by J. Moden, but for didactic purposes, I would like to see how the solution presented here can be updated to include the modern LEAD/LAG and OVER additions to SQL Server, in lieu of my clumsy attempt below.
[font="Courier New"]
DECLARE @tvf table (pk int not null primary key, pos int, endpos int, token varchar(20))
INSERT INTO @tvf(pk, pos) VALUES (0, 0)
DECLARE
@strInput VARCHAR(8000) = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16',
@delimiter VARCHAR(5) = ',';
WITH E1(N) AS ...
-- Replace the last SELECT statement in the original solution with:
INSERT INTO @tvf(pk, pos)
SELECT f.posnum, f.pos
FROM findchar f
UPDATE t2
SET endpos = t1.pos
FROM @tvf t2
INNER JOIN @tvf t1 ON t1.pk = t2.pk + 1
DELETE FROM @tvf WHERE pk = (SELECT MAX(pk) FROM @tvf)
UPDATE @tvf
SET token = SUBSTRING(@strInput, pos +1, endpos-pos-1)
WHERE NOT endpos IS NULL
SELECT * FROM @tvf[/font]