Using charindex AND substr to pull out data

[jmckown]

I'm attempting to pull a list of Google search terms from url's stored in a database. Here is an example of the data in the table I'm dealing with

http://www.google.com/search?hl=en&q=proxy
http://news.google.com/news?hl=en&q=proxy&um=1&ie=UTF-8&imgefp=Q-gUBRtWxEsJ&imgurl=cache.boston.com/resize/bonzai-fba/Globe_Photo/2008/06/19/1213929665_4899/539w.jpg
http://www.google.com/search?hl=en&q=proxy

I have written a query that gets me halfway there:

select user_id, substring (FULL_URL, charindex ('q=', FULL_URL)+2,100) as Query, full_url
from LOG_DETAILS where FULL_URL != '' AND FULL_URL LIKE '%en&q=%' AND CATEGORY = '76'

The only issue I have is that this will pull everything right of q= giving me this

proxy
proxy&um=1&ie=UTF-8&imgefp=Q-gUBRtWxEsJ&imgurl=cache.boston.com/resize/bonzai-fba/Globe_Photo/2008/0
proxy


now I would like to also remove the trailing info after the &.

Any Help is much appreciated, it's been awhile since I've used transact SQL so explanations are helpful.

Thanks,
Joel

[Delerna]

If I understand you correctly you need to
1) get the charindex of the first &
from the string to the right of that
2) get the charindex if the first &
If its zero choose the length of the string to the right of & in 1)
3) obtain the string between the 2 &'s

This is the basic idea, you can work out the details.
It can be done I have done it.
Ask if you have trouble, its a little mind boggling but not much.

I suggest tackling it bit by bit.
ie in a query return the whole string as a field
then add a field for the index of & no 1
then add a field for the string to the right of that
then add a field for the index of & no 2
then add a field that combines all of that to get the string you want

[jmckown]

I got it, here's the final query. Hopefully you can get the drift of what I did. Reverse, Substring and Charindex were the keys.

Thanks,
Joel

create table # temp ( query char (100))
insert into #temp (query)
select select substring (URL, charindex ('q=', URL)+2,100)
from sometable where URL LIKE '%en&q=%'


update #temp set query = reverse(substring (reverse(query), charindex ('mu&', reverse(query))+3,100)) where query like '%&um%'
update #temp set query = reverse(substring (reverse(query), charindex ('ei&', reverse(query))+3,100)) where query like '%&ie%'
update #temp set query = reverse(substring (reverse(query), charindex ('rf&', reverse(query))+3,100)) where query like '%&fr%'
update #temp set query = reverse(substring (reverse(query), charindex ('ml&', reverse(query))+3,100)) where query like '%&lmt%'
update #temp set query = reverse(substring (reverse(query), charindex ('ts&', reverse(query))+3,100)) where query like '%&start%

select * from #temp

[Delerna]

Glad to see you worked out a solution. Not how I would have done it but you learn more from working out your own solution, and it works to your satisfaction, so.....