SQL parse a long varchar field out with parameter/value pairs

Question

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');

Answer 1

What you're looking for is basically the opposite of the mysql Group_conat() function--instead of concating together a set, break it apart on a separator.

There's no easy one line query that covers this if you don't know the exact number of parameters. If you know the max number of parameters, you can write something like: SQL split comma separated row.

I suggest what you really want to do is write a procedure if you don't know the exact limit or it may change.

Here's another SO question that covers how you can achieve this with stored procedures: "Reverse GROUP_CONCAT" in MySQL?