0

I have a table called tableA, and the id field may stored values with comma at one record.
When I use sql: "select id from tableA", I will get the below result.

p

I do not know how to separate a row record into several rows like below:
27
19
7
18
...
Is it any hints for the sql script? Thank you.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Alpha Wong
  • 47
  • 4

1 Answers1

0

I'm not confident there isn't a nicer way, but maybe something like this is a good starting point:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(tablea.id, ',', numbers.row), ',', -1)
FROM
tablea INNER JOIN
-- This numbers subquery is taken from @Unreason's answer in https://stackoverflow.com/questions/304461/generate-an-integer-sequence-in-mysql
(
SELECT @row := @row + 1 AS row FROM 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,  
(SELECT @row:=0) n) numbers
on numbers.row <= LENGTH(tablea.id)-LENGTH(REPLACE(tablea.id, ',', ''))+1

Given tablea.id='27,19,8', SUBSTRING_INDEX(SUBSTRING_INDEX(tablea.id, ',', 1), ',', -1) will return the first element (27) and SUBSTRING_INDEX(SUBSTRING_INDEX(tablea.id, ',', 2), ',', -1) will return the second element (19), and so forth.

Therefore I join that with a list of numbers (in this case my list goes up to 100, so I am assuming a single tablea.id field never has more than 100 comma-separated values, although the details could be changed). The join condition uses LENGTH(tablea.id)-LENGTH(REPLACE(tablea.id, ',', ''))+1 which is a count of how many comma-separated values are in the field.

Here's a db fiddle of it working to play around with.

EdmCoff
  • 3,506
  • 1
  • 9
  • 9