Help Needed in Parsing Values From A URL Field

[adventurous1]

Hi...

I have some data that contains a column with a URL field. Within the URL field's contents, there are several values that I need to be able to parse out.

I can only do this with SQL (i.e. cant write a function since I dont have permissions to register/execute).

Can anyone help?

Thanks!


Example of URL field data

WT.tz=-6&WT.bh=17&WT.ul=en-us&WT.cd=32&WT.sr=1280x720&WT.jo=Yes&WT.ti=Veda%20Spa%20at%20Hotel%20Monaco%20in%20Denver,%20Colorado&WT.js=Yes&WT.jv=1.3&WT.ct=lan&WT.hp=0&WT.bs=1263x1773&WT.fv=10.0&WT.slv=Unknown&WT.tv=9.3.0&WT.sp=KC-DMN&WT.dl=0&WT.ssl=0&WT.es=

http://www.monaco-denver.com/veda-s...158&WT.co_f=204.96.147.217-324432272.30190918

Example of final format/output of data

->One column for every WT parameter and its corresponding value

WT.tz WT.bh WT.ul WT.cd WT.sr WT.jo WT.TI
6 17 en-us 32 1280x720 Yes Veda Spa at Hotel Monaco in Denver,Colorado

 

[simian336]

it is going to be a pain... Your going to have to use CHARINDEX to find the location of each WT.item then the next & symbol and then take everything in between as your value.

Here is an example of the first one...

declare @test varchar(5000)
declare @start int
declare @at int

set @test= 'WT.tz=-6&WT.bh=17&WT.ul=en-us&WT.cd=32&WT.sr=1280x720&WT.jo=Yes&WT.ti=Veda%20Spa%20at%20Hotel%20Monaco%20in%20Denver,%20Colorado&WT.js=Yes&WT.jv=1.3&WT.ct=lan&WT.hp=0&WT.bs=1263x1773&WT.fv=10.0&WT.slv=Unknown&WT.tv=9.3.0&WT.sp=KC-DMN&WT.dl=0&WT.ssl=0&WT.es=

http://www.monaco-denver.com/veda-s...58&WT.co_f=204.96.147.217-324432272.30190918'

select @start=charindex('WT.tz=',@test)+6
select @at=charindex('&',@test)
select @at-@start

select SUBSTRING(@test,@start, @at-@start)

Simi

 

[Qik3Coder]

http://www.logiclabz.com/sql-server...break-comma-separated-strings-into-table.aspx

select * 
from dbo.udfSplit('WT.tz=-6&WT.bh=17&WT.ul=en-us&WT.cd=32&WT.sr=1280x720&WT.jo=Yes&WT.ti=Veda%20Spa%20at%20Hotel%20Monaco%20in%20Denver,%20Colorado&WT.js=Yes&WT.jv=1.3&WT.ct=lan&WT.hp=0&WT.bs=1263x1773&WT.fv=10.0&WT.slv=Unknown&WT.tv=9.3.0&WT.sp=KC-DMN&WT.dl=0&WT.ssl=0&WT.es=[URL unfurl="true"]www.monaco-denver.com/veda-spa.html&WT.vt_f_tlh=1323388776&;WT.vtvs=1323385153721&WT.tsrc=Blazemore%20Hotels%20Properties&;WT.vt_tlh=1323388780&WT.co=Yes&WT.vt_sid=204.96.147.217-324432272.30190918.1323385158&WT.co_f=204.96.147.217-324432272.30190918'[/URL] , 'WT.')

tz=-6&
bh=17&
ul=en-us&
cd=32&
sr=1280x720&
jo=Yes&

That'll get you started.

You've got questions and source code. We want both!