Separate column in multiple lines (split)

Question

Separate column in multiple lines (split)

Navigation

#1 by (2 votes)

1

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

EX:

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

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

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

Can anyone help me perform this transfer via SQL MySQL?

mysql sql query select

asked by anonymous 13.09.2017 / 20:34

1 answer

Position JPanels vertically Return Id's That Are Not in the Database - MYSQL

score 2 · Accepted Answer

You can create a support table with the maximum number of separations 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 DISTINCT will filter the results equal.

You can check the result in SQL Fiddle .

You can get the maximum number of occurrences of - in your table with query as follows:

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

References: