WITH T AS ( SELECT '가나다' COL1, '다가나' COL2 FROM DUAL UNION ALL SELECT '홍길동' COL1, '김길동' COL2 FROM DUAL ) SELECT COL1 , COL2 , CASE WHEN INSTR( COL1, SUBSTR( COL2, 1, 1 )) > 0 AND INSTR( COL1, SUBSTR( COL2, 2, 1 )) > 0 AND INSTR( COL1, SUBSTR( COL2, 3, 1 )) > 0 THEN 'Y' ELSE 'N' END MATCH_YN FROM T ;
글자수가 고정이라면 이런식으로 가능합니다.
-- '가나다' col1, '다가가' col2 이런 케이스는 매칭이라고 볼수 없다면 아래와 같이 변경해봤습니다. WITH T AS ( SELECT '가나다' col1, '다가가' col2 FROM DUAL UNION ALL SELECT '가나다' , '다나가' FROM DUAL UNION ALL SELECT '가나다' , '다12나가' FROM DUAL UNION ALL SELECT '홍길동' , '김길동' FROM DUAL ) SELECT gb, CASE WHEN LISTAGG(SUBSTR(col1,lv,1)) WITHIN GROUP(ORDER BY SUBSTR(col1,lv,1)) = LISTAGG(SUBSTR(col2,lv,1)) WITHIN GROUP(ORDER BY SUBSTR(col2,lv,1)) THEN 'Y' ELSE 'N' END MA_YN FROM (SELECT col1, col2, ROW_NUMBER() OVER(ORDER BY col1) gb FROM t), (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 10) WHERE lv <= GREATEST(LENGTH(col1),LENGTH(col2)) GROUP BY gb