updating datastring 1

[balllian]

I have been given this weblog to analyse. i have been asked to clean up the data in this file by updating the data on the file.

One of the fields shows the origin of the web search with a datastring containing the web address containing one of the following 'co.uk' or '.com' or '.net'

how can i put togther a query that will delete data to the right of the examples above. This would then leave me with just the web address and no coding.

Thanks in advance

 

[traingamer]

If I understand your request, you could try something like:

If InStr(strOld,".com") then
    strNew = left(strOld,InStr(strOld,".com")-1) 
  else if InStr(strOld,".co.uk") then
      strNew = left(strOld,InStr(strOld,".co.uk")-1)
    else if
...

or something along those lines.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[balllian]

i have got this code already which sort of does what i require but as soon as i adapt it it doesnt work.

UPDATE Combined SET Referrer = Left([Referrer],InStr([Referrer],'?profile')+7)
WHERE Referrer Like '*?profile*';

I have chnaged this to the following and it doesn't work.

UPDATE Combined SET Referrer = Right([Referrer],InStr([Referrer],'?co.uk')+5)
WHERE Referrer Like '*?co.uk*';

 

[PHV]

You wanted this (as already suggested by Greg) ?
UPDATE Combined SET Referrer = Left([Referrer],InStr([Referrer],'.co.uk')-1)
WHERE Referrer Like '*.co.uk*';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[balllian]

Thanks for clarifying this for me. When i use this though it deletes the '.co.uk'. I need this to remain as we have 2 websites of the same name but they end as '.com' and also '.co.uk'. I need to differentiate between the 2

Thanks again

 

[PHV]

I guess you've discovered yourself the little tweak:
UPDATE Combined SET Referrer = Left([Referrer],InStr([Referrer],'.co.uk')[!]+5[/!])
WHERE Referrer Like '*.co.uk*';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[balllian]

Thanks again. Much appreciated

 

[traingamer]

My mistake. Do as suggested for .co.uk (i.e. +5) and for .com use +3