How to split string into multiple rows in a table using MYSQL?

Question

I have a table with Four different columns. Its called Box Table. It contains characteristics of different boxes.

Following are the names of the columns: Serial Number, Box Height, Box Width, Box Weight.

The first column (Serial Number) is a string, but it can contain multiple boxes in a single row e.g: (212,234). If a string contains multiples boxes, I want to split that string into different rows (the number of boxes, determined by number of commas + 1). When I do this, I want other column values to replicate for the newly created row(s) as well. e.g

Serial Number: 1,2 | Box Height: 5 | Box Width: 2 | Box Weight: 100

After transformation:

Row (1): Serial Number: 1 | Box Height: 5 | Box Width: 2 | Box Weight: 100
Row (2): Serial Number: 2 | Box Height: 5 | Box Width: 2 | Box Weight: 100

I cannot understand how to even begin this problem. The most I can do is remove the special characters from the string.

Which version of mysql are you using?

GMB
– GMB

2019-09-09 21:55:59 +00:00
Commented Sep 9, 2019 at 21:55 — GMB
– GMB, Commented Sep 9, 2019 at 21:55

GMB · Accepted Answer · 2019-09-09 22:08:33Z

One typical solution is to generate a list of numbers (by creating a table or using an inline query) and then JOIN it with the source data, using MySQL string functions to extract the relevant parts of serial_number.

Here is a solution that uses an inline query and that can handle up to 5 serials per record (to handle more serials, you would need to expand the subquery with more UNION ALLs):

SELECT
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(box.serial_number, ',', num.n), 
        ',', 
        -1
    ) new_serial_number,
    box.height,
    box.width,
    box.weight
FROM
    (
        SELECT 1 n
        UNION ALL SELECT 2
        UNION ALL SELECT 3
        UNION ALL SELECT 4
        UNION ALL SELECT 5
    ) num
    INNER JOIN box 
        ON CHAR_LENGTH(box.serial_number) 
               - CHAR_LENGTH(REPLACE(box.serial_number, ',', '')) 
           >= num.n - 1

This demo on DB Fiddle with your sample data returns:

| new_serial_number | height | width | weight |
| ----------------- | ------ | ----- | ------ |
| 1                 | 5      | 2     | 100    |
| 2                 | 5      | 2     | 100    |

Collectives™ on Stack Overflow

How to split string into multiple rows in a table using MYSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related