Split column into multiple rows (split)

Question

I have a table cidade_bairro with several cities and neighborhoods. I want to transfer this data to a new table, but in several lines instead of being in one.

EX:

Cidade  Bairros<br>
SP |    Consolação-Morumbi-Saude

In the new table "city_bairro2" would be separated into 3 lines:

Cidade  Bairro
SP |    Consolação
SP | Morumbi
SP | Saude

Could someone help me perform this transfer via SQL Mysql?

Answer 1

You can create a support table with the number maximum separations that neighborhoods can have. More or less as follows:

CREATE TABLE numeros (
  numero int
);

INSERT INTO numeros(numero)
VALUES(1), (2), (3), (4), (5),
      (6), (7), (8), (9), (10);

And use the following query to extract the data:

SELECT DISTINCT cb.cidade,
                SUBSTRING_INDEX(SUBSTRING_INDEX(cb.bairros, '-', n.numero), '-', -1) AS bairro
  FROM cidade_bairro cb
       CROSS JOIN numeros n
 ORDER BY cb.cidade, n.numero;

Note that the DISTINCT will filter the same results.

You can check the result on SQL Fiddle.

---

You can achieve the maximum number of occurrences of - in your table with the query next:

SELECT MAX(ROUND((LENGTH(cb.bairros) - LENGTH(REPLACE(cb.bairros, '-', ''))) / LENGTH('-'))) + 1 AS quantidade
  FROM cidade_bairro cb

---

References:

• Split string in Mysql
• SQL split values to Multiple Rows
• Count the number of occurrences of a string in a VARCHAR field?