what is alternative for select string_split('r,a,d,o',',') in mysql

Question

How to convert a string into rows in MySQL.

select id from month_tab where name in ( string_split('jan,feb,mar,may') );

In the above example, we need to return string_split('jan,feb,mar,may') as rows. the final output should be like select id from month_tab where name in ('jan','feb','mar','may') );

You asked how to convert delimited string into rows - the first two duplicates answer the question. However, if you want to imitate in operator with delimited string, then see the 3rd duplicate. — Shadow, Feb 06 '23 at 09:23

score 0 · Accepted Answer · answered Feb 06 '23 at 09:24

You can do it using JSON_TABLE

with cte as (
  select 'jan,feb,mar,may' as months
)
select month_tab 
from cte
CROSS JOIN JSON_TABLE(CONCAT('["', REPLACE(months, ',', '","'), '"]'),
                      '$[*]' COLUMNS (month_tab TEXT PATH '$')) jsontable;

what is alternative for select string_split('r,a,d,o',',') in mysql

1 Answers1