1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | -- @은 특수문자로 대체해주세요.. 못만들겠어요.. 샘플데이타가 더힘듬.. WITH T AS ( SELECT 1 FLD_KEY , '123456A`234@5678@0530020' ACL_KEY_CODE FROM DUAL UNION ALL SELECT 2, '1234562`2345678@0530021`0530022' FROM DUAL UNION ALL SELECT 3, '1234563`23456@78`l' FROM DUAL UNION ALL SELECT 4, '1234564`234@5678`c' FROM DUAL UNION ALL SELECT 5, '1234565`23456@78`7' FROM DUAL ) SELECT * FROM ( SELECT FLD_KEY , REGEXP_SUBSTR(TXT, '[^`]+' ,1,LV) STR , lv FROM ( SELECT FLD_KEY , REPLACE (ACL_KEY_CODE,chr(13)||chr(10), '`' ) txt FROM T )a , ( SELECT LEVEL lv FROM DUAL CONNECT BY LEVEL <= 10 ) b WHERE lv <= LENGTH(TXT) - LENGTH( REPLACE (TXT, '`' , '' ))+1 ) WHERE LENGTH(STR) = 7 AND REGEXP_LIKE(STR, '^[[:digit:]]*$' ) ORDER BY FLD_KEY , lv |