Breaking a stored procedure string into temp table mysql

Question

I have the following format being accepted into my stored procedure

hat=blue,yellow:=:jacket=leather,jean:=:shoes=nike,puma,umbro,converse

So i would like to accept this and insert it into a temp table as

product | inventory
-------------------
hat     |  blue
-------------------
hat     |  yellow  
-------------------
jacket  |  leather
-------------------
jacket  |  jean 
-------------------
shoes   |  nike
-------------------
shoes   |  puma 
-------------------
shoes   |  umbro
-------------------
shoes   |  converse
-------------------

so I have the following stored procedure accepting this but i am struggling to break it into the parts (new to mysql)

Taken from an example here Split a string and loop through values in MySql Procedure and being modifying it a bit

DELIMITER $$

DROP PROCEDURE IF EXISTS `inventoryHandle` $$
CREATE PROCEDURE `inventoryHandle`(_list MEDIUMTEXT)
BEGIN

DECLARE _next TEXT DEFAULT NULL;
DECLARE _nextlen INT DEFAULT NULL;
DECLARE _value TEXT DEFAULT NULL;

 CREATE TEMPORARY TABLE productInventory (
     product VARCHAR(50) NOT NULL
    , inventory  VARCHAR(50) NOT NULL
  );

iterator:
LOOP
  IF LENGTH(TRIM(_list)) = 0 OR _list IS NULL THEN
    LEAVE iterator;
  END IF;

  SET _next = SUBSTRING_INDEX(_list,':=:',1);  -- gets me the hats=blue,yellow string
  SET _nextlen = LENGTH(_next);
  SET _value = TRIM(_next);

  INSERT INTO productInventory (product, inventory) VALUES (***); -- not sure how to handle here
  SET _list = INSERT(_list,1,_nextlen + 1,'');
END LOOP;

END $$

DELIMITER ;

*"Taken from an example"* [indeed](https://stackoverflow.com/a/37231257/1695906). Please remember to provide attribution. — Michael - sqlbot, Oct 15 '17 at 01:02
Thank you. What's amusing, here, is that my first thought was "I answered a very similar question, once..." — Michael - sqlbot, Oct 15 '17 at 01:07

score 1 · Accepted Answer · answered Oct 15 '17 at 01:27

You've found a decent example for doing this (if I do say so myself), although in principle, SQL is something of an awkward place to do it. Splitting a string and doing inserts is easier in a procedural language with easy regex support, like Perl. But sometimes it makes sense to do things in the database.

The important difference in what you are doing and what this code was written to accomplish is that you need to do a splittin operation twice -- nested. After splitting the key/value pairs, you then need to separate the key from the list of values and then use more splitting for each set of comma-separated values.

Since _value now contains hat=blue,yellow, you can further split the key from the values list, with SUBSTRING_INDEX(_value,'=',1) for the key and SUBSTRING_INDEX(_value,'=',-1) for the values list.

You are stuck where you are, because you still need to change that comma-separated list of values into something iteratable, to do the inserts... so you could modify this code pretty extensively... or you could just call a second copy of this code, in a second procedure, modified to accept the extracted key and values list, split the values list on commas, and do the inserts. The second copy of the procedure will not create the temp table, since it already exists.

Also, since the first copy of the procedure is not looking for , but rather for :=:, you'll need to modify it to remove the correct number of characters as it walks the string.

Change this...

  SET _list = INSERT(_list,1,_nextlen + 1,'');

...to this...

  SET _list = INSERT(_list,1,_nextlen + 3,'');

...because your delimiter is 3 characters long, rather than 1, as in the original example, and that's what this line does -- removes the value you just inserted, and the delimiter following it.

Breaking a stored procedure string into temp table mysql

1 Answers1