-4

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.

EoinS
  • 5,405
  • 1
  • 19
  • 32
Lance
  • 17
  • 4
  • 3
    You really should not be storing data in a relational database like this as 1) this is against the relational db principles 2) mysql has a limited set of text manipulation functions. If you absolutely must store multiple data points within a column, then use json or xml formats as those have support within mysql. Your format is not supported. You have to parse the string yourself and no solution will be elegant. You may fare better if you extract the strings to an external program with better string manipulation capabilities. – Shadow Jan 18 '22 at 15:52
  • 2
    The "elegant" way would be to normalise the data and store each atomic value in its own row, which is the essence of how relational databases [are intended to] work. Even an EAV model would be preferable. You might consider converting the data to a json array and using the built-in methods available. – Stu Jan 18 '22 at 15:57
  • 1
    `SUBSTRING_INDEX(SUBSTRING_INDEX(column, 'field2=', -1), ',', 1)`. And according WHERE. – Akina Jan 18 '22 at 16:42

2 Answers2

-1

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

EoinS
  • 5,405
  • 1
  • 19
  • 32
-1

This answer from @Akina's comment works perfectly.

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

And WHERE accordingly.

buddemat
  • 4,552
  • 14
  • 29
  • 49
Lance
  • 17
  • 4