How to split column values by comma and return it as an array-CodePudding

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"] ,
},

CodePudding user response：

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.

CodePudding user response：

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

It is, of course, possible to refine this, and leave out the NULL values ...
I will not convert this output to JSON for you ...