How to split comma separated string in new row using CONNECT BY and REGEXP?

SELECT REGEXP_SUBSTR('APPLE,BOB,CARS', '[^,]+', 1, LEVEL) FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT('APPLE,BOB,CARS', '[^,]+', 1)

select * from
(select 'GIOVANNI COSTELLO' writer, 'RODRIGUEZ' author from dual) tt
where exists
(select * from (
select trim(REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL)) as c1
from
(
select 'ARMIN RODRIGUEZ, GIOVANNI COSTELLO, RUBEN RODRIGUEZ ALARCON, RUEDIGER SKOCZOWSKY, XAVIER NAIDOO' as str
from dual) CONNECT BY LEVEL <= REGEXP_COUNT(str, '[^,]+', 1)
) where c1 = tt.writer);

select regexp_substr('abc:xyz:abc:pqr:xyz','[^:]+',1, level) str from dual

connect by level <= regexp_count('abc:xyz:abc:pqr:xyz','[^:]+', 1)