Mysql Split line to INSERT

Question

I am having a table1:

id - values
12 - 124,145,135
16 - 254,33,11,456,78

...

With SQL, how can I split the values to insert into another table to get:

INSERT INTO table2 (id,cat) VALUES 12, 124;
INSERT INTO table2 (id,cat) VALUES 12, 145;
INSERT INTO table2 (id,cat) VALUES 12, 135;
INSERT INTO table2 (id,cat) VALUES 16, 254;
...

Thank you!

Too complicated and cannot be done with just SQL, you must involve at least some stored procedure to do that. Better do it via some server-side script (like PHP) where the solution is more than simple. — mitkosoft, Apr 15 '16 at 14:47

score 1 · Answer 1 · answered Apr 15 '16 at 16:07

Here's how you could do it in MySQL:

INSERT INTO table2
SELECT id,
       REPLACE(substring(substring_index(vals, ',', i),
               length(substring_index(vals, ',', i - 1)) + 1), ',', '')
FROM (
    SELECT a.*, @i := if(@id = a.id, @i + 1, 1) i, @id := a.id
    FROM (
        SELECT *
        FROM table1 a
        INNER JOIN information_schema.global_status b ON 1 = 1
        ORDER BY a.id
    ) a
    INNER JOIN (SELECT @i := 0, @id := NULL) x
) a
WHERE i <= LENGTH(vals) - LENGTH(REPLACE(vals, ',', '')) + 1

Obviously, the main issue was to convert the second column to rows.

Here's how it works (you should start reading it from the inner queries):

INSERT INTO table2
SELECT id,
       /* Get i-th CSV value from vals (where i ranges between 1
          and number of generated rows) */
       REPLACE(substring(substring_index(vals, ',', i),
               length(substring_index(vals, ',', i - 1)) + 1), ',', '')
FROM (
    SELECT a.*,

           /* Generate an index for each row. The index values will
              go from 1 to n (the number of generated rows) for each row
              in table1 */
           @i := if(@id = a.id, @i + 1, 1) i,

           /* We keep a reference to the previous table1.id, so that 
              we can reset the counter when we move to the next row
              in table1 */
           @id := a.id
    FROM (
        /* Generate sufficient rows
           You should have at least: Count(table1.*) x MAX_CSV_VALUES_PER_ROW rows */
        SELECT *
        FROM table1 a
        /* I used information_schema.global_status, but you may use any other table
           or even create your own temporary table:
           SELECT 1 UNION ALL SELECT 2 ... UNION ALL SELECT n */
        INNER JOIN information_schema.global_status b ON 1 = 1
        ORDER BY a.id
    ) a
    INNER JOIN (SELECT @i := 0, @id := NULL) x
) a
WHERE
    /* Keep only the relevant values; the other values are duplicates.
       We're going to keep only those values with indices that are less
       or equal to the number of CSV values in vals */
    i <= LENGTH(vals) - LENGTH(REPLACE(vals, ',', '')) + 1;

Thanks a lot for your help – Erick Boileau Apr 25 '16 at 21:11 — Erick Boileau, Apr 25 '16 at 21:11

score 0 · Answer 2 · edited May 23 '17 at 12:01

0

Does this help you? Split strings using mysql

In case the number of concatenated values in the "values" column is unknown or varying I would go for the answer which uses a separate function (SPLIT_STR). Otherwise it's getting really complicated.

edited May 23 '17 at 12:01

Community

1
1

answered Apr 15 '16 at 14:44

siax

150
7

Mysql Split line to INSERT

2 Answers2