regex - MySQL Split Comma Separated String Into Temp Table

Question

Can you parse a comma separated string into a temp table in MySQL using RegEx?

'1|2|5|6' into temp table with 4 rows.

Answer 1

This is pretty much the same question as Can Mysql Split a column?

MySQL doesn't have a split string function so you have to do work arounds. You can do anything with the data once you split it using one of the methods listed on the answer page above.

You can loop over that custom function and break when it returns empty, you'll have to play and learn some syntax (or at least I would) but the syntax for a FOR loop in mysql is here: http://www.roseindia.net/sql/mysql-example/for.shtml

You can iterate over it, incrementing the position in the function below:

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

(Credit: https://blog.fedecarg.com/2009/02/22/mysql-split-string-function/ )

Which should return '' if no match is found, so break the loop if no match is found. This will allow you to with only mysql parse over the split string and run the insert queries into a temp table. But man why not just use a scripting language like php for that kind of work? :(

Code for loop syntax:

DELIMITER $$  

CREATE PROCEDURE ABC(fullstr)

   BEGIN
      DECLARE a INT Default 0 ;
      DECLARE str VARCHAR(255);
      simple_loop: LOOP
         SET a=a+1;
         SET str=SPLIT_STR(fullstr,"|",a);
         IF str='' THEN
            LEAVE simple_loop;
         END IF;
         #Do Inserts into temp table here with str going into the row
         insert into my_temp_table values (str);
   END LOOP simple_loop;
END $$