WITH t AS ( SELECT 1 no, '홍길동' nm FROM dual UNION ALL SELECT 2, 'A홍길동' FROM dual UNION ALL SELECT 3, '홍길동A' FROM dual UNION ALL SELECT 4, '홍A길동' FROM dual UNION ALL SELECT 5, 'A홍B길동' FROM dual ) SELECT no, nm FROM (SELECT no, nm , LAG (nm, 2) OVER(ORDER BY no) v1 , LAG (nm, 1) OVER(ORDER BY no) v2 , LEAD(nm, 1) OVER(ORDER BY no) v3 , LEAD(nm, 2) OVER(ORDER BY no) v4 FROM t ) WHERE REGEXP_LIKE(v1||','||v2||','||nm, '.*(...).*,.*\1.*,.*\1.*') OR REGEXP_LIKE(v2||','||nm||','||v3, '.*(...).*,.*\1.*,.*\1.*') OR REGEXP_LIKE(nm||','||v3||','||v4, '.*(...).*,.*\1.*,.*\1.*') ;
WITH t AS ( SELECT 1 no, '홍길동' nm FROM dual UNION ALL SELECT 2, 'A홍길동' FROM dual UNION ALL SELECT 3, '홍길동A' FROM dual UNION ALL SELECT 4, '홍A길동' FROM dual UNION ALL SELECT 5, 'A홍B길동' FROM dual ) SELECT DISTINCT a.no, a.nm FROM t a , t b , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99) c WHERE c.lv <= LENGTH(a.nm) - 2 AND a.no != b.no AND b.nm LIKE '%'||SUBSTR(a.nm, lv, 3)||'%' ;