_ms65g_ (5/20/2010)
In splitting string using number table is not this approch simpler?
DECLARE @s-2 VARCHAR(80)='Army,Navy,Air Force,Marines';WITH c AS
(SELECT 1 AS n
UNION ALL
SELECT n+1 FROM c WHERE n < 100)
SELECT i
FROM (SELECT CASE WHEN CHARINDEX(',', @s-2 + ',', n) - n = 0 THEN ''
ELSE SUBSTRING(@s, n, CHARINDEX(',', @s-2 + ',', n) - n)
END, n
FROM c
WHERE n <= LEN(@s)
) d(i, n)
WHERE SUBSTRING(',' + @s-2, n, 1) = ','
Sorry... got pulled away on other things...
Yes... that method is much "simpler". It's also much slower in the grand scheme of things because recursive counters are as bad or worse than While Loops for performance and they use a lot more "reads" even if the reads are in memory.
--Jeff Moden
Change is inevitable... Change for the better is not.