입력값 :
1. (C01 &C00F&S09A&T01E&T02A&E00E/E00G-300E) / (C01 &C00F&S09A&T01K&T02A&E00E/E00G)
2. (C01 &C08B/C08C-E00D) / (C01 &C08D)
3. C01 &210J/300E-G10K
4. C01 &300E&C00F&E00C&S09B&T01C&T02F/T02R
5. C01 -C00E/C00G
결과값
1. ([C01]C00 = C00F and [C01]S09 = S09A and [C01]T01 = T01E and [C01]T02 = T02A and [C01]E00 = E00E or [C01]E00 = E00G and [C01]300 != 300E) or ([C01]C00 = C00F and [C01]S09 = S09A and [C01]T01 = T01K and [C01]T02 = T02A and [C01]E00 = E00E or [C01]E00=E00G)
2. ([C01]C08 = C08B or [C01]C08 = C08C and [C01]E00 != E00D) or ([C01]C08 = C08D)
3. ([C01]210 = 210J or [C01]300 = 300E and [C01]G10 != G10K)
4. ([C01]300 = 300E and [C01]C00 = C00F and [C01]E00 = E00C and [C01]S09 = S09B and [C01]T01 = T01C and [C01]T02 = T02F or [C01]T02 = T02R)
5. ([C01]C00 != C00E or [C01]C00 = C00G)
감사합니다.
SET DEFINE OFF; WITH t AS ( SELECT 1 id, '(C01 &C00F&S09A&T01E&T02A&E00E/E00G-300E) / (C01 &C00F&S09A&T01K&T02A&E00E/E00G)' v FROM dual UNION ALL SELECT 2, '(C01 &C08B/C08C-E00D) / (C01 &C08D)' FROM dual UNION ALL SELECT 3, 'C01 &210J/300E-G10K' FROM dual UNION ALL SELECT 4, 'C01 &300E&C00F&E00C&S09B&T01C&T02F/T02R' FROM dual UNION ALL SELECT 5, 'C01 -C00E/C00G' FROM dual ) SELECT id , LISTAGG( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_SUBSTR(v, '\([^(]+', 1, lv) , '/(\w{3})(\w{1})', ' or \1 = \1\2') , '&(\w{3})(\w{1})', ' and \1 = \1\2') , '-(\w{3})(\w{1})', ' and \1 != \1\2') , '(and | or )', '\1['|| SUBSTR(REGEXP_SUBSTR(v, '\(...', 1, lv), 2) ||']' ) , '\w{3} (and|or) ', '') , '&', 'and') , '/', 'or') ) WITHIN GROUP(ORDER BY lv) v FROM (SELECT id, DECODE(SUBSTR(v, 1, 1), '(', v, '('||v||')') v FROM t) , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9) WHERE lv <= REGEXP_COUNT(v, '\(') GROUP BY id ;