Sql query, sub string issue

i have a little question to ask

i have a following data in database column .

isLoggedIn=False&CurrentReferral=http://ufone.testSite.tv/Messaging.aspx?aspxerrorpath=/abc.aspx&kw=&FirstVisitDate=10/10/2011 6:52:38 AM&FirstReferral=http://www.testSite.tv/CustomerCare.aspx?link=CustomerCare&ExternalReferrer=http://www.testSite.tv/CustomerCare.aspx?link=CustomerCare,http://testSite.tv/Signin.aspx&UserTotalVisits=2&ContactId=819&tenantChannelName=Ufone GSM channel&tenantChannelUrl=http://ufone.testSite.tv/&mcid=&ASP.NET_SessionId=ksntwa55yh3u3s45xn5rysyj&IsVisitTracked=yes

what i need to do is to get just only 'ExternalReferrer' from that, like

ExternalReferrer=http://www.t开发者_开发技巧estSite.tv/CustomerCare.aspx?link=CustomerCare,http://testSite.tv/Signin.aspx&UserTotalVisits=2&ContactId=819&tenantChannelName=Ufone GSM channel&tenantChannelUrl=http://ufone.testSite.tv/

i tried sub string but unablle to get that

declare @myData nvarchar (1000)
set @myData= 'isLoggedIn=False&CurrentReferral=http://ufone.testSite.tv/Messaging.aspx?aspxerrorpath=/abc.aspx&kw=&FirstVisitDate=10/10/2011 6:52:38 AM&FirstReferral=http://www.testSite.tv/CustomerCare.aspx?link=CustomerCare&ExternalReferrer=http://www.testSite.tv/CustomerCare.aspx?link=CustomerCare,http://testSite.tv/Signin.aspx&UserTotalVisits=2&ContactId=819&tenantChannelName=Ufone GSM channel&tenantChannelUrl=http://ufone.testSite.tv/&mcid=&ASP.NET_SessionId=ksntwa55yh3u3s45xn5rysyj&IsVisitTracked=yes'



SELECT  SUBSTRING(@myData, CHARINDEX ('ExternalReferrer' ,@myData ), CHARINDEX ('mcid' ,@myData )) AS Initial 

m using sql 2008

---

The third parameter of SUBSTRING is the length of the string to be returned, so you have to subtract the position of &mcid from the position of external:

select 
    substring
    (
        @mydata, 
        charindex('external', @mydata), 
        charindex('&mcid', @mydata) - charindex('external', @mydata)
    )

---

EDIT:

What data are you running my SQL statement on?
I used the MyData declaration from your question.

This piece of code runs on my machine in SQL Server Management Studio 2008 and returns exactly the desired output as shown in your question:

declare @myData nvarchar (1000)
set @myData= 'isLoggedIn=False&CurrentReferral=http://ufone.testSite.tv/Messaging.aspx?aspxerrorpath=/abc.aspx&kw=&FirstVisitDate=10/10/2011 6:52:38 AM&FirstReferral=http://www.testSite.tv/CustomerCare.aspx?link=CustomerCare&ExternalReferrer=http://www.testSite.tv/CustomerCare.aspx?link=CustomerCare,http://testSite.tv/Signin.aspx&UserTotalVisits=2&ContactId=819&tenantChannelName=Ufone GSM channel&tenantChannelUrl=http://ufone.testSite.tv/&mcid=&ASP.NET_SessionId=ksntwa55yh3u3s45xn5rysyj&IsVisitTracked=yes'

select 
    substring
    (
        @mydata, 
        charindex('external', @mydata), 
        charindex('&mcid', @mydata) - charindex('external', @mydata)
    )

---

EDIT 2:

The second parameter of SUBSTRING specifies the start position of the returned characters.
In my first answer, this was the first character of "ExternalReferrer=" because of the charindex(...).

If you want to omit "ExternalReferrer=" from the result, you just have to add the length of this string to the second SUBSTRING parameter, so that the returned string begins directly after "ExternalReferrer=" :

select 
    substring
    (
        @mydata, 
        charindex('external', @mydata) + len('externalreferrer='), 
        charindex('&mcid', @mydata) - charindex('external', @mydata)
    )

---

EDIT 3:

My mistake.
In my last edit, I moved the start position of the returned characters to the right.
But I didn't change the length of the returned string, so of course the end position moved to the right as well (that's why the &mcid= stuff was returned again)
The solution is not only to add the length of "ExternalReferrer=" to the starting position, but to subtract it from the ending position as well:

select 
    substring
    (
        @mydata, 
        charindex('external', @mydata) + len('externalreferrer='), 
        charindex('&mcid', @mydata) - charindex('external', @mydata) - len('externalreferrer=')
    )

---

I believe that this is what you want:

SELECT SUBSTRING(@myData, PATINDEX('%ExternalReferrer%' ,@myData ), PATINDEX('%mcid%' ,@myData ) - PATINDEX('%ExternalReferrer%' ,@myData ) - 1) AS Initial  

But is very specific for that string, so you need to be careful if you have another 'mcid' string in your url.

---

If you know that every time mcid will come right after ExternalReferrer then try running this regular expression in your asp.net code:

/ExternalReferrer=(.*)&mcid/

Matching group $1 will give you the value that you are looking for.r