Oracle REGEXP_REPLACE

SELECT REGEXP_REPLACE(TEST,'(ire_v0[0-2][0-9]*)', 'decode(\1, null, 0, \1)') REG_REP
FROM
(
SELECT '((ire_v001/ire_v002*100)*0.5+(ire_v003/ire_v020*100)*0.5)' TEST
FROM dual
)

--> 결과
((decode(ire_v001, null, 0, ire_v001)/decode(ire_v002, null, 0, ire_v002)*100)*0.5+(decode(ire_v003, null, 0, ire_v003)/decode(ire_v020, null, 0, ire_v020)*100)*0.5)

-- 정규식 테스트(위 내용과 관계없음.)

WITH tmp AS

(

    SELECT 1 idx, '(90)abcdefg' txt   FROM DUAL UNION ALL

    SELECT 2, '(80) abcdefg'          FROM DUAL UNION ALL

    SELECT 3, '70맒ㄴ;람낢ㄴ'         FROM DUAL UNION ALL

    SELECT 4, '60 ㄻㅇㄴㄻㄴㅇㄹ'     FROM DUAL UNION ALL

    SELECT 5, '50 ㅁㄴㄻㄴㄴㅁ 40'    FROM DUAL UNION ALL

    SELECT 6, 'ㅎㅎㅎㅎㅎㅎㅎㅎ'      FROM DUAL UNION ALL

    SELECT 7, 'ㅋㅋㅋㅋㅋㅋㅋ 40'     FROM DUAL

)

SELECT  idx, txt, regexp_replace(txt, '^(\([0-9\)]+)(.*)', '\1점 \2')

FROM    tmp

WHERE   regexp_like(txt, '^(\([0-9\)])');

ELECT oi_sub_linkedcode, oi_sub_linkedcodename

FROM

(

    SELECT  REGEXP_SUBSTR(oi_sub_linkedcodes,     '[^,]+', 1, LEVEL) oi_sub_linkedcode

           ,REGEXP_SUBSTR(oi_sub_linkedcodenames, '[^,]+', 1, LEVEL) oi_sub_linkedcodename

    FROM (SELECT oi_sub_linkedcodes, oi_sub_linkedcodenames FROM NG02_ORGAN_INFO WHERE oi_code = 57)

    CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(oi_sub_linkedcodes, '[^,]+'))  + 1

)

WHERE oi_sub_linkedcode = '50901240003'

SELECT  TRIM(COLUMN_VALUE) str 

FROM    (SELECT 'aaaa,bbbb,cccc,dddd' str FROM DUAL)

       ,XMLTABLE(('"' || REPLACE(str, ',', '","') || '"'))

SELECT COLUMN_VALUE oi_sub_linkedcodes

FROM   TABLE(SPLIT((SELECT REGEXP_REPLACE(REGEXP_REPLACE('50901240001,50901240002,50901240003,50901240004,50901240005,50901240006,50901240007', '(50901240001|50901240002|50901240004|50901240005|50901240008)', ','), '([^0-9],)', '') FROM DUAL)))

SELECT oi_sub_linkedcodes

FROM

(

       SELECT COLUMN_VALUE oi_sub_linkedcodes FROM TABLE(SPLIT('50901240001,50901240002,50901240003,50901240004,50901240005,50901240006,50901240007', ','))

       MINUS

       SELECT COLUMN_VALUE oi_sub_linkedcodes FROM TABLE(SPLIT('50901240001,50901240002,50901240004,50901240005,50901240008', ','))

)

그 밖에
REGEXP_LIKE
REGEXP_INSTR
REGEXP_SUBSTR 등이 존재하니 참고하시기 바랍니다.

http://www.psoug.org/reference/regexp.html

http://www.adminschool.net/wiki/doku.php?id=dbms:oracle:devel:regexp