Jeff Moden (1/23/2011)
You bet. Thanks for the feedback, the tips, and a couple of other things.As a side bar, recursive CTE's tend to be a bit slow and resource intensive for doing splits. You might want to consider one of the other methods.
Everything is relative!
My recursive CTE was EXTREMELY FAST and I was more than happy with it until I compared against the tally table alternative, when suddenly it became EXTREMELY SLOW!!
SET NOCOUNT ON
--Supress the auto-display of rowcounts for appearance/speed
DECLARE @StartTime datetime
--Timer to measure total duration
SET @StartTime = GETDATE()
--===== Split or "Normalize" the whole table at once
SELECT mh.lcid ,row_number() over ( partition by mh.lcid order by t.N),
SUBSTRING(',' + mh.months + ',', N + 1,
CHARINDEX(',', ',' + mh.months + ',', N + 1) - N - 1) AS Value
FROM dbo.Tally t
CROSS JOIN sys.syslanguages mh
WHERE N < LEN(',' + mh.months + ',')
AND SUBSTRING(',' + mh.months + ',', N, 1) = ','
--===== Display the total duration
SELECT STR(DATEDIFF(ms, @StartTime, GETDATE())) + ' Milliseconds duration'
SET @StartTime = GETDATE()
;With Months as
(select sll.lcid,sll.shortmonths,1 as MonthNumber, 1 as Position, charindex(',', sll.shortmonths,2) as endPosition FROM sys.syslanguages sll
UNION ALL
select sl.lcid, sl.shortmonths,m.MonthNumber+1, m.endPosition+1 as StartPosition, CHARINDEX(',',sl.shortmonths+',', m.endPosition+1) from sys.syslanguages sl
inner join Months m on m.lcid=sl.lcid
where CHARINDEX(',',sl.shortmonths, m.Position+1)<>0
) --SELECT lcid, m.shortmonths,m.Position, m.endPosition , m.endPosition-m.Position as sublength, len(m.shortmonths) as lenght FROM Months m
SELECT lcid, substring(m.shortmonths,m.Position, m.endPosition-m.Position+1) FROM Months m
SELECT STR(DATEDIFF(ms, @StartTime, GETDATE())) + ' Milliseconds duration'