Mysql - Separating line values into multiple lines

Question

I need to separate the values of a line into several lines, taking into account that it has line that has up to 2000 values separated by ,

Answer 1

Consulting some references to be able to split a string in Mysql, I arrived at the following precedent. Which will traverse the entire table you quoted, dismember the values from the "Pens" column and insert into a new row in a temporary table.

CREATE PROCEDURE `SPLIT_COLUNA`()
BEGIN
    DECLARE var_fim INT DEFAULT FALSE;
    DECLARE var_indice INT;
    DECLARE var_valor VARCHAR(255);
    DECLARE var_valores TEXT;


    -- Cria um novo cursor com todos os registros da tabela 
    -- cujo a coluna queremos separar os valores em linhas.
    DECLARE var_cursor CURSOR FOR
        SELECT
            opens
        FROM
            valores;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET var_fim = TRUE;

    OPEN var_cursor;


    -- Tabela temporária que irá guardar os valores separadamente.
    CREATE temporary TABLE resultado(OPEN VARCHAR(255));


    -- Percorre cada linha para obter a string que iremos dividir.
    LEITURA_COLUNAS: LOOP
        FETCH var_cursor INTO var_valores;
        SET var_indice = 0;

        -- Aqui ocorre a mágica. Realizando o split da string.
        SPLIT_COLUNA: LOOP
            SET var_indice = var_indice + 1;

            SET var_valor = REPLACE(
                SUBSTRING(
                    SUBSTRING_INDEX(var_valores, ",", var_indice),
                    LENGTH(
                        SUBSTRING_INDEX(var_valores, ",", var_indice - 1)
                    ) + 1
                ),
                ",",
                ''
            );

            IF var_valor = '' THEN
                LEAVE SPLIT_COLUNA;
            END IF;

            INSERT INTO resultado VALUES(var_valor);
        END LOOP SPLIT_COLUNA;

        IF var_fim THEN
            LEAVE LEITURA_COLUNAS;
        END IF;
    END LOOP;

    -- Encerra o cursor.
    CLOSE var_cursor;

    -- Exibe todos os dados da tabela temporária.
    SELECT * FROM resultado;

    -- Excluimos a tabela temporária para liberar recurso.
    DROP temporary TABLE resultado;
END

How the split was accomplished

Let’s assume that the "Opens" column of a given row has the following value "12345:US,56789:BR,98765:US".

First round var_indice = 1.

SET var_indice = 0; -- Índice inicia com zero

SPLIT_COLUNA: LOOP
    SET var_indice = var_indice + 1; -- var_indice = 1

    SET var_valor = REPLACE(
        SUBSTRING(
            -- Corta a string(12345:US,56789:BR,98765:US) até a 
            -- ocorrência da primeira vírgula. Resultado: "12345:US".
            -- Somente está linha já erá suficiente para obter o primeiro
            -- valor. As demais irei explicar quando o loop for para 
            -- segunda posição.
            SUBSTRING_INDEX(var_valores, ",", var_indice), 
            LENGTH(
                SUBSTRING_INDEX(var_valores, ",", var_indice - 1)
            ) + 1
        ),
        ",",
        ''
    );

    IF var_valor = '' THEN
        LEAVE SPLIT_COLUNA;
    END IF;

    INSERT INTO resultado VALUES(var_valor);
END LOOP SPLIT_COLUNA;

Second round var_indice = 2.

SET var_indice = 0;

SPLIT_COLUNA: LOOP
    SET var_indice = var_indice + 1; -- var_indice = 2

    -- O replace esta aqui caso a string possua uma vírgula ao final.
    -- Assim removendo a mesma.
    SET var_valor = REPLACE(
        -- Portanto aqui realizamos o corte da parte que não queremos.
        -- Ficando SUBSTRING("12345:US,56789:BR", 9)
        -- Pegando parte da string após o nono carácter.
        -- Resultado: 56789:BR
        SUBSTRING(
            -- Corta a string(12345:US,56789:BR,98765:US) até a 
            -- ocorrência da segunda virgula. Resultado:"12345:US,56789:BR".
            -- Repara que o primeiro valor retornou junto desta vez.
            SUBSTRING_INDEX(var_valores, ",", var_indice), 
            -- Portanto temos que eliminar o primeiro valor.
            -- Obtendo o tamanho da string que foi resultada abaixo 
            -- somamos mais um para eliminar a virgular que está após o valor. 
            -- Resultado: 8 + 1 = 9.
            LENGTH(
                -- Obtém os valores anteriores que já guardamos.
                -- Corta a string até a última virgula que realizamos a leitura.
                -- Resultado: "12345:US"
                SUBSTRING_INDEX(var_valores, ",", var_indice - 1)
            ) + 1
        ),
        ",",
        ''
    );

    IF var_valor = '' THEN
        LEAVE SPLIT_COLUNA;
    END IF;

    INSERT INTO resultado VALUES(var_valor);
END LOOP SPLIT_COLUNA;

Upshot

Table with the data

Result

References

Mysql - Create function type SPLIT / Create Function with SPLIT delimited strings

Mysql Split String Function