key value 파싱 방법

다음과 같은 4가지의 케이스가 CLOB 형태로 관리되고 있습니다.

item#1,item#2,...item#n 의 값을 뽑아내고 싶습니다.

 

케이스1
a)item#1; 10 
b)item#2; 20


케이스2
a)item#1; 2 
b)item#2; 3


케이스3
a)item#1; 5
          & 4 
b)item#2; 3


케이스4
a)item#1; 4 
c)item#3; 3

 

1. 단순한 key value 파싱

 

REGEXP_SUBSTR(FT,'(item#1);(.*)',1,1,'i',1)  구문으로 쉽게 key값을 가져올 수 있습니다.

정규표현식 (item#1);(.*)의 두 번째 괄호가 value 이므로 REGEXP_SUBSTR(FT,'(item#1);(.*)',1,1,'i',2) 로 하면 value 를 추출합니다.

SELECT 
CN  --Case No
,FT -- Full Text 
,REGEXP_SUBSTR(FT,'(item#1);(.*)',1,1,'i',1) KS -- Key String 
,REGEXP_SUBSTR(FT,'(item#1);(.*)',1,1,'i',2) VS_SIMPLE
--,REGEXP_SUBSTR(FT,'(item#1);(.*)b\)',1,1,'in',2) VS
--,REGEXP_SUBSTR(FT,'(item#1);(.*)([b-z]\))?',1,1,'in',2) VS
,REGEXP_SUBSTR(FT,'(item#1);(.+?)([b-z]\))',1,1,'in',2) VS -- Value String
FROM (
SELECT
1 AS CN, 
'a)item#1; 10 
b)item#2; 20 ' AS FT FROM DUAL 
UNION ALL
SELECT
2 AS CN, 
'a)item#1; 2 
b)item#2; 3 ' AS FT FROM DUAL
UNION ALL
SELECT
3 AS CN, 
'a)item#1; 5
            & 4 
b)item#2; 3 ' AS FT FROM DUAL
UNION ALL
SELECT
4 AS CN, 
'a)item#1; 4 
c)item#3; 3 ' AS FT FROM DUAL
)

 

 

2. value 내부에 줄바꿈이 있는 경우

 

REGEXP_SUBSTR(FT,'(item#1);(.*)',1,1,'i',2) 한 가지 단점이 있습니다.

3번 케이스는 5 & 4 로 추출해야 하는데 줄바꿈이 있어서 5로 추출되는 한계가 있습니다.

어떻게 해야 할까요?

 

첫 번째, 'i' 를 'in' 으로 수정합니다.
REGEXP_SUBSTR(FT,'(item#1);(.*)',1,1,'in',2)
i 는 대소문자를 무시한다는 옵션이고 n 은 줄바꿈 문자 다음도 계속 파싱하라는 옵션입니다.

 

이렇게 수정하고 쿼리를 수행하면, & 4 뿐만 아니라 다음 라인의 b)item#2; 3 까지 모두 가져올 것이다.

b) 이전까지만 가져오도록 수정하려면 어떻게 해야 할까요? 
b)를 정규식에 추가해야합니다. 다만 ) 앞에는 \를 추가해서 \) 로 해야한다.
REGEXP_SUBSTR(FT,'(item#1);(.*)b\)',1,1,'in',2)

 

이렇게 하면 당장의 문제는 해결하지만, 새로운 문제가 또 다시 발생합니다.
4번 케이스에서 추출에 문제가 발생합니다. 
왜일까요? a)다음에 b)항목이 없어서입니다.

 

a) b) c)... 등의 패턴을 [a-z]\)으로 일반화하여 표현합니다.

해당 패턴이 다음 항목으로 올 수도 있고 안올 수도 있으므로 괄호로 묶어 ? 처리해줍니다.

추출 항목 다음에 여러 개의 패턴이 있는 경우, greedy 하게 가져오는 것을 방지하기 위해 (+?) 로 수정해주어야 완성됩니다.

REGEXP_SUBSTR(FT,'(item#1);(.+?)([a-z]\))?',1,1,'in',2)

 

3. Oracle reference 

'n' allows the period (.), which is the match-any-character character, to match the newline character. If you omit this parameter, then the period does not match the newline character.
https://docs.oracle.com/database/121/SQLRF/functions161.htm#SQLRF20014

 

 

4. 실전 사례 

조직검사지에서 PIN 정보를 추출해야 합니다. 

 

<검사지중 관련부분>

...

     f) perineural invasion; absent
     g) presence of PIN; present
           low grade (Rt4, Rt5, Lt1)
           high grade (Rt1, Rt2, Rt3, Rt4, Rt5, Lt1)  
[DIAGNOSIS 1]

 

<추출 예시>

REGEXP_SUBSTR(fulltext, '(.*PIN *[;:])(.+?)(\[|h\))', 1, 1, 'in', 2)