How to implode the value of a record to multiple rows using MySQL?

Question

I have a table like this:

// mytable
+----+--------+-------------------+
| id |  word  |      numbers      |
+----+--------+-------------------+
| 1  | hello  | 1<br>2<br>3<br>   |
| 2  | how    | 12<br>15<br>      |
| 3  | are    | 453<br>1<br>      |
| 4  | you    | 3<br>33<br>453<br>|
+----+--------+-------------------+

And I want this output:

// mynewtable
+----+--------+---------+
| id |  word  | numbers |
+----+--------+---------+
| 1  | hello  | 1       |
| 2  | hello  | 2       |
| 3  | hello  | 3       |
| 4  | how    | 12      |
| 5  | how    | 15      | 
| 6  | are    | 453     |
| 7  | are    | 1       |
| 8  | you    | 3       |
| 9  | you    | 33      |
| 10 | you    | 453     |
+----+--------+---------+

I can do that using PHP. First I have to fetch all rows and implode them by <br> then insert them again. But now I want to know, is there any better solution? (specially using pure MySQL)

Possibly this would be a good time to refactor a rather badly designed piece of your database — RiggsFolly
– RiggsFolly, Commented Dec 2, 2015 at 13:41
If you can replace the <br> with a single character then Can I resolve this with pure mysql? joining on ';' separated values in a column maybe interesting. You could change the functions to use <br> as the separator. It will give the output you want. — Ryan Vincent
– Ryan Vincent, Commented Dec 2, 2015 at 13:50
What @RiggsFolly said - time to refactor and fix things. Whoever designed the first model had no clue how databases are used. You can either hack around and try to get it working, facing numerous problems along the way or you can simply create a table that stores the info like you've shown in the output example. Good luck, I do hope you decide to do it the right way because that will be the least amount of work. — Mjh
– Mjh, Commented Dec 2, 2015 at 13:56
I agree with @Mjh. While Ryan Vincent points you in the right direction in terms of selecting/adjusting data in a query, the next problem you then face is to turn your selected data into an INSERT query. It's just going to get very complex and messy very quickly. — Byson
– Byson, Commented Dec 2, 2015 at 14:04
Just to make it clear. I always use normalized tables. The only reason I did that answer was to find some 'standard way' of dealing with these 'pesky' foreign keys in delimited strings. The functions are companions of FIND_IN_SET ;-/ — Ryan Vincent
– Ryan Vincent, Commented Dec 2, 2015 at 14:30

Simhachalam Gulla · Accepted Answer · 2015-12-02 14:01:01Z

2

Try this solution through query

SELECT @row := @row + 1 as row, t.word, t.number FROM (
    SELECT
      word,
      SUBSTRING_INDEX(SUBSTRING_INDEX(numbers, '<br>', n.digit+1), '<br>', -1) number
    FROM split_rec
    INNER JOIN 
        (SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
        ON LENGTH(REPLACE(numbers, '<br>' , '')) <= LENGTH(numbers)-n.digit
    ORDER BY id, n.digit
) AS t, (SELECT @row := 0) r 
WHERE t.number <> ''

Output

row word    number
1   hello   1
2   hello   2
3   hello   3
4   how     12
5   how     15
6   are     453
7   are     1
8   you     3
9   you     33
10  you     453

answered Dec 2, 2015 at 14:01

Simhachalam Gulla

9881 gold badge7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Byson · Accepted Answer · 2015-12-02 13:54:07Z

0

While I think that it might be possible using stored procedures, MySQL variables and a liberal use of LIKE "%...%"statements, it would be an order of magnitude more complex than a PHP solution.

Furthermore, I think it is not possible at all if you don't have access to stored procedures as you would need dynamically create new INSERT statements based on what you find in that last column.

TL;DR: No, make yourself happy and use PHP.

answered Dec 2, 2015 at 13:54

Byson

5504 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to implode the value of a record to multiple rows using MySQL?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related