Parsing Values Out of A URL Field
Parsing Values Out of A URL Field
(OP)
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=www.mo
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
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=www.mo
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
Talk To Other Members
RE: Parsing Values Out of A URL Field
Are the different WT-labels always in this order? If that's the case it may end up with something like
SELECT SUBSTRING(col FROM POSITION('WT.tz=' IN col) + 6
FOR POSITION('WT.bh=' IN col) -
POSITION('WT.tz=' IN col) - 7) AS "WT.tz",
SUBSTRING(col FROM POSITION('WT.bh=' IN col) + 6
FOR POSITION('WT.ul=' IN col) -
POSITION('WT.bh=' IN col) - 7) AS "WT.bh"
etc
You may also want to use CAST to reduce the length of the selected values.
RE: Parsing Values Out of A URL Field
Thanks for the snippet. When I tried to use, it gives me an error about a mismatched input around the "FROM" where it is expecting a ")" in the expression specification. From this, I have tried to add the additional parenthesis but still get an error. Sorry to be such a pain but can you review?
Thanks,
Tony
RE: Parsing Values Out of A URL Field
you do realize that you asked this question in the ANSI SQL forum, and not, for instance, in forum183: Microsoft SQL Server: Programming
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Parsing Values Out of A URL Field
substring(arg1,arg2[,arg3])
or
substr(arg1,arg2[,arg3])