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

Mysql – How to split column values by comma and return it as an array

As you can see below I have Name column. I want to split it by / and return the value in array.

MyTable

Id Name
1 John/Warner/Jacob
2 Kol

If I write a query as

Select Id, Name from MyTable

it will return

{
        "id": 1,
        "name": "John/Warner/Jacob",
},
{
        "id": 2,
        "name": "Kol",
},

Which query should I write to get below result ?

{
        "id": 1,
        "name": ["John", "Warner", "Jacob"],
},
{
        "id": 2,
        "name": ["Kol"] ,
},
Tags:

2

Answers


  1. 0

    Don’t think you can return an array in the query itself, but you could do this…

    SELECT id,
  SUBSTRING_INDEX(name, '/', 1) 
    AS name_part_1,
  SUBSTRING_INDEX(name, '/', -1) 
    AS name_part_2
FROM tableName;

    Only way to build it as an array would be when processing the result accordingly in whatever language you are using.

    Login or Signup to reply.
  2. 0

    You can define a function split, which is based on the fact that substring_index(substring_index(name,'/',x),'/',-1) will return the x-th part of a name when separated by ‘/’.

    CREATE FUNCTION `test`.`SPLIT`(s varchar(200), c char, i integer) RETURNS varchar(200) CHARSET utf8mb4
    DETERMINISTIC
BEGIN
  DECLARE retval varchar(200);
  WITH RECURSIVE split as (
    select 1 as x,substring_index(substring_index(s,c,1),c,-1) as y, s 
    union all 
    select x+1,substring_index(substring_index(s,c,x+1),c,-1),s from split where x<= (LENGTH(s) - LENGTH(REPLACE(s,c,''))) 
  )
  SELECT y INTO retval FROM split WHERE x=i ;
  return retval;
END

    and then do:

    with mytable as (
   select 1 as Id, 'John/Warner/Jacob' as Name
   union all
   select 2, 'Kol')
select
    id, split(Name,'/',x) as name
from mytable
cross join (select 1 as x union all select 2 union all select 3) x
order by id, name;

    output:

    Id name
    1 Jacob
    1 John
    1 Warner
    2 [NULL]
    2 [NULL]
    2 Kol
    1. It is, of course, possible to refine this, and leave out the NULL values …
    2. I will not convert this output to JSON for you …
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search