데이터베이스 질문과 답변
입력값 :
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
;
마농님. 감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.