Can MySQL Split Comma-Separated Strings into a Temporary Table?

Can MySQL Split Comma Separated String Into Temp Table?

Splitting comma-separated strings into a temporary table is a common task in data manipulation. While MySQL lacks a built-in function for this task, it can be achieved using alternative methods.

One approach involves creating a user-defined function that implements string splitting. The SPLIT_STR function from the provided answer can be used for this purpose. It iteratively extracts substrings based on a delimiter and position, returning an empty string when no match is found.

To iterate over the split string and populate a temporary table, a loop can be employed. A FOR loop can be used to incrementally call the SPLIT_STR function, and the results can be inserted into the temporary table using INSERT queries.

However, it's important to note that this approach requires custom coding and knowledge of MySQL's loop syntax. An alternative solution would be to use a scripting language such as PHP or Python to perform the string splitting and insert the results into the temporary table.

Here's the code for a MySQL loop that iterates over the split string and inserts values into a temporary table:

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

The above is the detailed content of Can MySQL Split Comma-Separated Strings into a Temporary Table?. For more information, please follow other related articles on the PHP Chinese website!