Parse out parameter/value pairs from one large varchar field

[Brad Blake]

Hi all,

Our application is running on MySQL 5.6.21, and for every transaction, it writes out an entry into a Log table. The primary field I am interested in is a long varchar field, and it basically has the full URL that is passed in to our application. This data field can have any number of parameter/value pairs in it, that are separated by an ampersand &. I am trying to come up with a way to parse them out, so that we can report on this data.

Here's an example record:
sessionId=LongSessionIdWeCanIgnore&serverurl=https://www.theurl.com/bla/bla&parameter1=123456789&param2=23456789&par3=987654&p4=abcdef123&parameterlongname6=ABCDEFG

So from this field, I'd like to have a SQL statement/function that separates it out as follows:

Parameter Value
--------------- ----------------------
serverUrl https://www.theurl.com/bla/bla
parameter1 123456789
param2 23456789
par3 987654
p4 abcdef123
parameterlongname6 ABCDEFG

So as you can see, this SQL needs to be able to loop through, and for each ampersand that it sees, pull out the parameter name (from the & to the =), and the value that is after the = sign (from the = to the next &). I've got the SQL worked out that is "hard-coded" for one parameter, but I want to avoid this, as we have over 300 different parameters possible, and I don't want to have to write separate code for each one. Example SQL below for the hard-coded one I've got so far:

select
substr(ParameterString,(instr(ParameterString, '&parameter1=')+12), (locate('&', ParameterString, instr(ParameterString, '&parameter1=')+12)) - (instr(ParameterString, '&parameter1=')+12))
from TestLogTable
where ParameterString like '%&parameter1=%';

Again, I don't like this method as it's got to be hardcoded with the number 12, as that is the length of '&parameter1='.

I'm hoping some of the SQL gurus here can help me get this thing more dynamic and automated?? Sample SQL below to create a test table with some test data.

Thank you!!


create table TestLogTable (ParameterString varchar(32768));

insert into TestLogTable (ParameterString) values ('sessionId=LongSessionIdWeCanIgnore&serverurl=https://www.theurl.com/bla/bla&parameter1=123456789&param2=23456789&par3=987654&p4=abcdef123&parameterlongname6=ABCDEFG');
insert into TestLogTable (ParameterString) values ('sessionId=LongSessionIdWeCanIgnore&serverurl=https://www.theurl2.com/bla/bla&parameter5=123456789&param6=23456789&par7=987654&p8=abcdef123&parameterlongname9=ABCDEFG');
insert into TestLogTable (ParameterString) values ('sessionId=LongSessionIdWeCanIgnore&serverurl=https://www.theurl2.com/bla/bla&parameter5=123456123&param6=23456789123&par7=987654123&p8=abcdef123123&parameterlongname9=ABCDEFG123123');