I found this one somewhere on SqlTeam.com forums…
DECLARE @sep char(1), @s nvarchar(1024)
SET @sep = ','
SET @s = 'a,b,c';
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 1024 END) AS s
FROM Pieces
Technorati tags: SQL