I have one column in MySQL which is return me comma separated value , I want to convert that column in to rows.
Better answer then How to convert comma separated parameters to rows in mysql?
select value from table limit 1
response
| value |
|---|
| honda,activa,pleasure,car |
I want this value to row like
| value |
|---|
| honda |
| activa |
| pleasure |
| car |
CREATE TABLE response (id INT, value TEXT)
SELECT 1 id, 'honda,activa,pleasure,car' value;
SELECT response.id, jsontable.value
FROM response
CROSS JOIN JSON_TABLE(CONCAT('["', REPLACE(value, ',', '","'), '"]'),
'$[*]' COLUMNS (value TEXT PATH '$')) jsontable;
| id | value |
|---|---|
| 1 | honda |
| 1 | activa |
| 1 | pleasure |
| 1 | car |
PS. The query assumes that the value does not contain duoble quotes. If they are present then they must be quoted. See https://dbfiddle.uk/HUmPZEo1
First we need to create function which return index value of comma separated value
CREATE FUNCTION `SPLIT_STR`(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
) RETURNS varchar(255) CHARSET utf8mb3
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '')
Then Create virtual recursive table from current value
with recursive new_table as (
select value ,LENGTH(t.value) - LENGTH(REPLACE(t.value, ',', '')) as n ,1 as x from table t limit 1
union all
select value, n,1+x as x from new_table where x <= n
)
select TRIM(SPLIT_STR(value,',',x)) as value from new_table
will return
| value |
|---|
| honda |
| activa |
| pleasure |
| car |