Sql string manipulation

Out of string -
\\servr1\Reports\Temp\Big_Ticket_Posting.rpt^\\servr\DF^Start_Date=2/2/2007;End_Date=2/2/2007;Compensation=ALL;Channel=ALL;Territory=W06;^test@test.com^Big Ticket Posting^PDF

How can I parse ( select ) out by Sql 2005 parameters and their values. End result I need is:
Start_Date=2/2/2007;End_Date=2/2/2007;Compensation=ALL;Channel=ALL;Territory=W06
I tried with charindex() no avail. Thanks for help!
ivan_belalAsked:
Who is Participating?
 
imitchieConnect With a Mentor Commented:
single select

declare @s varchar(max) set @s =
'\\servr1\Reports\Temp\Big_Ticket_Posting.rpt^\\servr\DF^Start_Date=2/2/2007;End_Date=2/2/2007;Compensation=ALL;Channel=ALL;Territory=W06;^test@test.com^Big Ticket Posting^PDF'

select substring(left(@s, len(@s) - charindex(';', reverse(@s))), 1+charindex('=', @s) -
charindex('^', reverse(left(@s, charindex('=', @s)-1))), len(@s))

assumptions:
the first "=" is after the first parameters
the last ";" ends the parameters
0
 
pai_prasadCommented:
DECLARE @QueryStr varchar(1024)
                  ,@startPosition int
                  ,@endPosition int

SELECT @queryStr = '\\servr1\Reports\Temp\Big_Ticket_Posting.rpt^\\servr\DF^Start_Date=2/2/2007;End_Date=2/2/2007;Compensation=ALL;Channel=ALL;Territory=W06;^test@test.com^Big Ticket Posting^PDF
'
Select @startPosition  =  charindex('^',@querystr,charindex('^',@querystr)+1)
Select @endPosition    =  charindex('^',@querystr,@startPosition+1)

SELECT @startPosition, @endPosition

Select substring(@queryStr,@startPosition+1,(@endPosition-@startPosition)-1)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.