How to convert comma separated string value into rows in MySQL?

Question

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

8.0.29 Mysql Version – Harikrushna Patel Nov 07 '22 at 13:27 — Harikrushna Patel, Nov 07 '22 at 13:27

score 4 · Accepted Answer · answered Nov 07 '22 at 13:30

4

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

fiddle

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

answered Nov 07 '22 at 13:30

Akina

39,301
5
14
25

This query won't work if data is not regular, it will throw JSON error – Harikrushna Patel Nov 19 '22 at 18:37
@HarikrushnaPatel There is no a query which can work correctly on any data. Sanitize your data. – Akina Nov 19 '22 at 18:57

score 0 · Answer 2 · answered Nov 07 '22 at 13:36

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

How to convert comma separated string value into rows in MySQL?

2 Answers2