WITH T1 ( c1 , c2 ) AS ( WITH T1 ( c1 , c2 ) AS ( SELECT '영수' , '사람' FROM DUAL UNION ALL SELECT '철수' , '사람' FROM DUAL UNION ALL SELECT '영희' , '사람' FROM DUAL UNION ALL SELECT '아찌' , '개' FROM DUAL UNION ALL SELECT '치맥' , '닭' FROM DUAL ) , T2 ( c1 ) AS ( SELECT '영수@철수@영희' FROM DUAL ) SELECT * FROM T1 a , (SELECT REGEXP_SUBSTR(c1,'[^@]+',1,lv) c1 FROM T2 , (SELECT LEVEL lv FROM DUAL CONNECT BY LEVEL <= 10 ) T3 WHERE T3.lv <= LENGTH(T2.c1) - LENGTH(REPLACE(T2.c1,'@')) + 1 ) b WHERE a.c1 = b.c1