Unnesting strings in Redshift and MySQL

mysql.sql

create table books (tags varchar(1000));

insert into books values
	('A, B, C, D'),
	('D, E'),
	('F'),
	('G, G, H')
;

select
  TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(B.tags, ',', NS.n), ',', -1)) as tag
from (
  select 1 as n union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6 union all
  select 7 union all
  select 8 union all
  select 9 union all
  select 10
) NS
inner join books B ON NS.n <= CHAR_LENGTH(B.tags) - CHAR_LENGTH(REPLACE(B.tags, ',', '')) + 1

redshift.sql

create table books (tags varchar(1000));

insert into books values
	('A, B, C, D'),
	('D, E'),
	('F'),
	('G, G, H')
;

with NS AS (  
  select 1 as n union all
  select 2 union all
  select 3 union all
  select 4 union all
  select 5 union all
  select 6 union all
  select 7 union all
  select 8 union all
  select 9 union all
  select 10
)
select  
  TRIM(SPLIT_PART(B.tags, ',', NS.n)) AS tag
from NS  
inner join books B ON NS.n <= REGEXP_COUNT(B.tags, ',') + 1

redshift_json_array.sql

create table books (tags varchar(1000));

insert into books values
	('["A", "B", "C", "D"]'),
	('["D", "E"]'),
	('["F"]'),
	('["G", "G", "H"]')
;


with NS AS (
	select 1 as n union all
    select 2 union all
    select 3 union all
    select 4 union all
    select 5 union all
    select 6 union all
    select 7 union all
    select 8 union all
    select 9 union all
    select 10
)
select
  TRIM(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(B.tags, NS.n - 1)) AS val
from NS
inner join books B ON NS.n <= JSON_ARRAY_LENGTH(B.tags)