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
You are quite right jcrawf02. I decided test your theory about patindex and charindex and also include a check on the like as well. I created a query to count the number of rows (+-29000) with like, patindex and charindex and I must say that the difference in duration was very visible. The like and the patindex both gave me 33 milliseconds where the charindex gave me 13 milliseconds. I was a bit apprehensive about your post at first but now I am thankful. I have learned how to tune my stored procedure thanks to a part of Jeff Moden's Tally table method and also not to take all functions and methods at face value.
Thanks a lot.:P:P:P:P;););)
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)