Oracle 18c

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

create table tab (id, email) as
select 1, null from dual union all
select 2, 'mail1@domain.com' from dual union all
select 3, 'mail1@domain.com; mail2@domain.com, mail3@mail.com' from dual union all
select 4, 'mail1@domain.com, mail2@domain.com' from dual union all
select 5, 'mail1@domain.com mail2@domain.com' from dual;
5 rows affected

select id, email from tab;


ID
EMAIL


1
null

2
mail1@domain.com

3
mail1@domain.com; mail2@domain.com, mail3@mail.com

4
mail1@domain.com, mail2@domain.com

5
mail1@domain.com mail2@domain.com


SELECT tab.id, jsontable.email_num, jsontable.email
FROM tab
CROSS JOIN JSON_TABLE('["' || REGEXP_REPLACE(tab.email, '[,;\/ ]+', '","') || '"]',
                      '$[*]' COLUMNS (email_num FOR ORDINALITY,
                                      email VARCHAR(255) PATH '$')) jsontable


ID
EMAIL_NUM
EMAIL


1
1
null

2
1
mail1@domain.com

3
1
mail1@domain.com

3
2
mail2@domain.com

3
3
mail3@mail.com

4
1
mail1@domain.com

4
2
mail2@domain.com

5
1
mail1@domain.com

5
2
mail2@domain.com

ID	EMAIL
1	null
2	mail1@domain.com
3	mail1@domain.com; mail2@domain.com, mail3@mail.com
4	mail1@domain.com, mail2@domain.com
5	mail1@domain.com mail2@domain.com