How do I pull out specific strings from a comma separated string?-CodePudding

I have a string like so;

"field1=lance,field2=peter,field3=john"

The actual string has 20 fields, and I want to pull out specific values by name.

For instance, I want to pull out the value for "field2" and return the value "peter",

Can someone give me an elegant way of doing this in MySQL?

I should mention that this is a standard field format coming out of an eCommerce system. I have no control over the format. It would be possible to extract the data cleanly through the API, but that would be significant extra work, especially as I have the data already in this format.

CodePudding user response：

interesting question. There is a lot below so let's break it down. We essentially build a query and execute the stmt

DELIMITER $$

DROP PROCEDURE IF EXISTS proc_loop_test$$
CREATE PROCEDURE proc_loop_test()

#create empty query string
set @sqlstring = '';

#set your string of fields, not sure where this comes from
set @mystring = 'field1=lance,field2=peter,field3=john';

#create number of times we will loop through the string
set @num = (select length(@mystring)
- length(replace('field1=lance,field2=peter,field3=john',',',''))  1);

#init loop
loop LOOP
#create a short string just taking the "last" field/value pair
set @shtstring = (select SUBSTRING_INDEX(@mystring,',',-1));

#recreate your query string removing the substring we created
set @mystring = (select left(replace(@mystring,@shtstring,''),length(replace(@mystring,@shtstring,''))-1));

#add to your query string, we will build this for each 
set @sqlstring = concat(@sqlstring ,(select 
concat('''',SUBSTRING_INDEX(@shtstring,'=',-1),'''' 
,' as ', 
left(@shtstring,(position('=' in @shtstring) -1))) ),',');
#reduce our count by one as we have removed the latest field
set @num = @num - 1;

#leave the loop when no fields left
if @num = 0 then leave LOOP;
end if;
end loop LOOP;

END$$

DELIMITER ;

#create a query statement to execute
set @query = (select concat('select ',left(@sqlstring, length(@sqlstring)-1)));

#execute the query!
PREPARE stmt FROM @query;
EXECUTE stmt;

Result

field3  field2  field1
john    peter   lance

There is no array logic, this would be simple in presto SQL etc. Because you have an arbitrary number of fields being defined at any time we are going to need to loop, and unfortunately you cannot loop in mysql without creating a procedure

That is the first few lines. We also create our full string from your source and the number of iterations (number of fields in string).

Then basically we isolate the "last" field/value pair iterively, rearrange each one so field1=john turns into more sql friendly 'john' as field',

We reduce our counter and string each time we loop through until counter is 0. At which point we stop.

We then prepare a query with our value/field pairs and a 'select' string. Then execute and you get your values as fields

Credit Dynamic Strings prepare/exec Looping and stored procs Simulating Split function

CodePudding user response：

This answer from @Akina's comment works perfectly.

SUBSTRING_INDEX(SUBSTRING_INDEX(column, 'field2=', -1), ',', 1)

And WHERE accordingly.