CODEWITHSUNDEEP
mysqldatabasestring
Kot Szutnik
Kot Szutnik

Reputation: 105

How to separate multiple data from one column?

I've got a problem - I need to separate data from column Column3 (separator '|').

Column1 Column2 Column3
7        23      567|568|85|532|296|581|300|265|577|330|563|423|55|442
8         0      242
9         0      242
10       23      567|568|85|532|296|581|300|265|577|330|563|423|55|442
14        4      330|563|423|134|242

Column1 is ID, Column2 is '|' count for each row, Column 3 is data which should be separate in new rows.

My output should look like:

Column1 Column4
7       567
8       242
9       242
10      567
14      330
7       568
10      568
14      563

I wrote union like below, but I don't want iterate it 60 times...

select 
    Column1,
    substring_index(substring_index(Column2,'|',1),'|',-1) as Column2
from Table1
union
select
    Column1,
    substring_index(substring_index(Column2,'|',2),'|',-1) as Column2
from Table1

Could you help me to find a better solution?

BR

Upvotes: 1

Views: 198

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can do this with a list of number. Here is an example:

select Column1,
       substring_index(substring_index(Column2,'|', nums.n),'|',-1) as Column2
from Table1 t1 cross join
      (select (n1.n - 1) * 12 + (n2.n - 1)*3 + n3.n as n
       from  (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
             ) n1 cross join
             (select 1 as n union all select 2 union all select 3 union all select 4 
             ) n2 cross join
             (select 1 as n union all select 2 union all select 3 
             ) n3
      ) nums

Upvotes: 2

Related Questions