IF OBJECT_ID('tempdb..#concatmonth') IS NOT NULL BEGIN DROP TABLE #concatmonth END

create table #concatmonth(monthname varchar(255))
insert #concatmonth(monthname)select 'January:February:March:April:May:June:July:August:September:October:November:December'

-- create a table to store results in
IF OBJECT_ID('tempdb..#MyHead') IS NOT NULL BEGIN DROP TABLE #MyHead END
create table #MyHead (PK int identity(1,1),
			monthnames varchar(255))
--original solution
select monthname, LEFT(monthname,PATINDEX('%:%',monthname)-1) as firstmonth, 
SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)) secondpart,
LEFT(SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)),PATINDEX('%:%',SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)))-1) secondmonth,
SUBSTRING(SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)),PATINDEX('%:%',SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN( monthname)))+1,LEN(SUBSTRING(monthname,PATINDEX('%:%',monthname)+1,LEN(monthname)))) thirdmonth
from #concatmonth

--new solution
DECLARE @currentLocation int, @currentString varchar(255)
SET @currentLocation = 1
SET @currentString = (SELECT monthname FROM #concatmonth)

WHILE charindex(':',@currentString,@currentLocation)>0
BEGIN
	INSERT INTO #MyHead
	SELECT substring(@currentString,1,charindex(':',@currentString,@currentLocation)-1)

	SELECT @currentString = substring(@currentString,charindex(':',@currentString,@currentLocation)+1,len(@currentString)-charindex(':',@currentString,@currentLocation)+1)
END
-- one last time to catch last iteration without ending ':'
INSERT INTO #myHead
SELECT @currentString

SELECT * FROM #MyHead ORDER BY PK