Split a CSV field into different rows in SQL -

a colleague of mines encountered problem while working on cobol program , solved @ application level. still curious if possible solve on data access level, sql. somehow related this other question, i'd use ansi sql.

i'm looking single sql select query acts on varchar field contains variable length csv rows. purpose of query split every csv field in own result set row.

here example schema , data (and here fiddle):

create table table1 (`field` varchar(100));   insert table1 (`field`)       values              ('hello,world,!')    ,              ('haloa,!')          ,              ('have,a,nice,day,!'); 

here output i'd have query:

hello world ! haloa ! have nice day ! 

the csv separator used comma, , wouldn't worry escaping.

as far can tell, ansi sql:

with recursive word_list (field, word, rest, field_id, level) (                select field,           substring(field 1 position(',' in field) - 1) word,          substring(field position(',' in field) + 1) rest,          row_number() on () field_id,          1   table1   union    select c.field,           case              when position(',' in p.rest) = 0 p.rest             else substring(p.rest 1 position(',' in p.rest) - 1)           end word,          case              when position(',' in p.rest) = 0 null             else substring(p.rest position(',' in p.rest) + 1)           end rest,           p.field_id,          p.level + 1   table1 c     join word_list p on c.field = p.field , position(',' in p.rest) >= 0 )  select word word_list order field_id, level; 

this assumes values in field unique.

here running example: http://rextester.com/nars7464