WITH T ( ID , NM )AS ( SELECT 'AAA' , '홍길동' FROM DUAL UNION ALL SELECT 'BBB' , '이순신' FROM DUAL UNION ALL SELECT 'CCC' , '안중근' FROM DUAL UNION ALL SELECT 'DDD' , '안창호' FROM DUAL ), T_IN ( TXT ) AS ( SELECT '홍길동,홍길동,홍길동,이순신,이순신' FROM DUAL ) SELECT A.ID , A.NM FROM T A , ( SELECT REGEXP_SUBSTR(TXT,'[^,]+',1,LEVEL ) NM FROM T_IN CONNECT BY LEVEL <= ( SELECT REGEXP_COUNT(TXT,',') FROM T_IN ) + 1 ) B WHERE A.NM = B.NM