David McKinney (1/28/2011)

---

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'