I need to separate the values of a line into several lines, taking into account that it has a line that has up to 2000 values separated by
Querying some references to be able to split a string in MySql, I came up with the following procedure. That will run through the entire table you've cited, break apart the values in the "opens" 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 done
Let's assume that the "opens" column of a given line 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;
Result
Table with data
Resultprocedure
References
MySql - Create function type SPLIT / Create function with SPLIT delimited strings