MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytest (
  id INT,
  vals VARCHAR(255));

INSERT INTO mytest VALUES 
(1,'tea,coffee,bread,milk'),
(2,'carrot,cabbage,broccoli,lettuce,tomato');

SELECT *, 
       length(vals), 
       LENGTH(REPLACE(vals,',','')),
      MAX(LENGTH(vals)-LENGTH(REPLACE(vals,',',''))+1) OVER (PARTITION BY id) 
  FROM mytest;

Records: 2  Duplicates: 0  Warnings: 0




id
vals
length(vals)
LENGTH(REPLACE(vals,',',''))
MAX(LENGTH(vals)-LENGTH(REPLACE(vals,',',''))+1) OVER (PARTITION BY id)


1
tea,coffee,bread,milk
21
18
4

2
carrot,cabbage,broccoli,lettuce,tomato
38
34
5


WITH RECURSIVE cte AS (
SELECT 1 AS ctr, MAX(LENGTH(vals)-LENGTH(REPLACE(vals,',',''))+1) AS fLen FROM mytest 
  UNION ALL
SELECT ctr+1, fLen FROM cte WHERE ctr+1 <= fLen
  )
SELECT * FROM cte


ctr
fLen


1
5

2
5

3
5

4
5

5
5


/*without considering `id` column - group by is to eliminate any duplicates*/
WITH RECURSIVE cte AS (
SELECT 1 AS ctr, MAX(LENGTH(vals)-LENGTH(REPLACE(vals,',',''))+1) AS fLen FROM mytest 
  UNION ALL
SELECT ctr+1, fLen FROM cte WHERE ctr+1 <= fLen
  )
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(vals,',',ctr),',',-1) AS valsAsRows
  FROM cte
 CROSS JOIN mytest
  GROUP BY valsAsRows;


valsAsRows


bread

broccoli

cabbage

carrot

coffee

lettuce

milk

tea

tomato


/*considering `id` column - if any - the difference is we don't need `GROUP BY` here*/
WITH RECURSIVE cte AS (
SELECT id, 1 AS ctr, MAX(LENGTH(vals)-LENGTH(REPLACE(vals,',',''))+1) AS fLen 
   FROM mytest 
  GROUP BY id
  UNION ALL
SELECT id, ctr+1, fLen FROM cte WHERE ctr+1 <= fLen
  )
SELECT ctr, 
       SUBSTRING_INDEX(SUBSTRING_INDEX(vals,',',ctr),',',-1) AS valsAsRows
  FROM cte
  JOIN mytest
ON cte.id=mytest.id;


ctr
valsAsRows


4
milk

3
bread

2
coffee

1
tea

5
tomato

4
lettuce

3
broccoli

2
cabbage

1
carrot

id	vals	length(vals)	LENGTH(REPLACE(vals,',',''))	MAX(LENGTH(vals)-LENGTH(REPLACE(vals,',',''))+1) OVER (PARTITION BY id)
1	tea,coffee,bread,milk	21	18	4
2	carrot,cabbage,broccoli,lettuce,tomato	38	34	5

ctr	valsAsRows
4	milk
3	bread
2	coffee
1	tea
5	tomato
4	lettuce
3	broccoli
2	cabbage
1	carrot