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

Splitting Comma-Separated Strings into a Temp Table in MySQL

Using MySQL, it is not possible to directly split a comma-separated string into a temp table using regular expressions. This is because MySQL lacks a built-in split string function.

Workaround Solutions

Since direct splitting is not feasible, you can employ various workaround methods:

1. Splitting using Custom Functions

You can create a custom function in MySQL to split the string into individual values. For example:

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

2. Using a Loop

Call the custom function within a loop and increment the position until an empty value is returned:

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;
      # Insert into temp table here with str going into the row
      insert into my_temp_table values (str);
   END LOOP simple_loop;
END $$

3. Scripting Language Integration

Alternatively, you can utilize a scripting language like PHP to split the string and perform the necessary operations.

Conclusion

While MySQL lacks a dedicated split string function, these workaround methods enable you to effectively parse comma-separated strings into temp tables. Choose the approach that best suits your requirements and preferences.

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