j-1064772 (5/12/2015)

---

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]