How to Split a Comma-Separated String into a MySQL Temp Table Using Regular Expressions?

MySQL Split Comma Separated String into Temp Table Using RegEx

MySQL lacks a dedicated split string function, necessitating alternative approaches. One method involves emulating a split by iterating over a custom function.

Custom Split Function

The following function splits a string x delimited by delim at position pos:

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

Looping and Inserting into a Temp Table

To iterate over the split string and insert into a temp table, use a LOOP statement:

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 $$

Note: Using a scripting language like PHP for such tasks may be more efficient than relying solely on MySQL.

The above is the detailed content of How to Split a Comma-Separated String into a MySQL Temp Table Using Regular Expressions?. For more information, please follow other related articles on the PHP Chinese website!