Question 112818 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0 
    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search