1

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.testSite.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

Improve this question
2
  • What error do you get - I copied and pasted your code and it worked fine Commented Oct 11, 2011 at 13:29
  • It gives no error, but it doesn't return the desired result (see the "what I need to do" part in the question). Commented Oct 11, 2011 at 13:33

3 Answers 3

Reset to default
1

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=')
    )
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

this is what i got Msg 537, Level 16, State 2, Line 2 Invalid length parameter passed to the LEFT or SUBSTRING function.
wow, its working but can i omit that External from final result?
@mahisama: Yes, see my answer (edited again). If my answer was helpful, it would be nice if you accepted it as the correct answer (and upvote it, but you can't do that yet, you need 15 rep. for that).
am geting this part too in result.. &mcid=&ASP.NET_Se which i dont want
this is the last this i need. =(
|
1

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.

Improve this answer

Comments

1

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

Improve this answer

2 Comments

This isn't a valid SQL statement
Tags mentioned with the question also contain asp.net, so answer has code that can be executed in asp.net

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.