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

You can use regular expression in MySQL to specify a pattern for a complex search, you cannot parse the strings.

But you can build INSERT query with the help of REPLACE and CONCATENATE to save data to temp table.

Answer 2

I have done this, for when you don't have table values and so on:

select *
from(
    select c, SUBSTRING_INDEX(SUBSTRING_INDEX('1|2|5|6', '|', c+1), '|', -1) as name
    from(
        SELECT (TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue + TWO_32.SeqValue) c
        FROM (
            SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16 
            CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 32 SeqValue) TWO_32
    ) as b
    WHERE c <= (CHAR_LENGTH('1|2|5|6') - CHAR_LENGTH(REPLACE('1|2|5|6', '|', '')))
) as a;

May not be the best answer, but works without aid of functions and procedures, no additional tables etc.