jcrawf02 (9/11/2008)

---

Your solution works if the original set is small, like your example of 'January:February:March', but as you pointed out, gets ugly the larger your string gets.

Two things -

1) use CHARINDEX, not PATINDEX, because you can specify a starting point in CHARINDEX. Then it's just a matter of finding the previous ':' and going from there. By the way, you don't actually need the '%' in your search string when using CHARINDEX, you can just search for CHARINDEX(':',monthname)

2) you can use a loop to find these values (see below) but the best way to do this, especially if you have either an undetermined length or a large value, is to use a Tally or Numbers table. See Jeff Moden's article on Tally tables here:http://www.sqlservercentral.com/articles/TSQL/62867/, including splitting a string.

Here's a sample loop that will accomplish this for the year's worth of months, not nearly as efficient as a tally table, but does the job.

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