ORACLE 정규표현식 SUBSTR, REPLACE, INSTR, DECODE, TRIM 사용하기

ORACLE 정규표현식 SUBSTR, REPLACE, INSTR, DECODE, TRIM 사용하기

1. 정규식을 사용해 전화번호 값을 자리수 마다 컬럼 나누기

-- 전화번호 컬럼으로 나누기
SELECT SUBSTR(PHONE,1,INSTR(PHONE,'-',1,1)-1) AS 첫번호 -- 1번째 자리부터 3개 숫자만 출력
     , SUBSTR(PHONE,INSTR(PHONE,'-',1,1)+1,((INSTR(PHONE,'-',1,2)-INSTR(PHONE,'-',1,1))-1)) AS 중간번호 -- 5번째 자리부터 9-4-1 = 4개 숫자만 출력
     , SUBSTR(PHONE, INSTR(PHONE,'-',1,2)+1, 4) AS 끝번호 -- 10번째 자리부터 4개 숫자만 출력
  FROM (
        SELECT '010-1234-5678' PHONE  FROM DUAL
       )

INSTR은 찾을 문자열의 위치 값을 숫자로 반환함.

INSTR('기준이 되는 문자열','찾을 문자열', 기준이 되는 문자열에서 비교를 시작할 위치, 검색된 결과의 순번)

PHONE 문자열에 '-' 가 들어가있는 첫번째 자릿수의 값을 나타내는것.

EX)

SELECT '010-1234-5678' PHONE  FROM DUAL

INSTR(PHONE,'-',1,1) 의 출력 값은 4

INSTR(PHONE,'-',1,2) 의 출력 값은 9

SUBSTR은 찾을 문자열을 문자로 반환함.

SUBSTR('기준이 되는 문자열','시작할 자리수','몇번째까지 찾을 숫자')

SELECT '010-1234-5678' PHONE  FROM DUAL

SUBSTR(PHONE,1,3)

PHONE의 1(첫번째 문자부터) ~ 3(세번째 문자까지) 문자를 출력

010

SUBSTR(PHONE,5,4)

PHONE의 5(다섯번째 문자부터) ~ 4(네번째 문자까지) 문자를 출력

1234

SUBSTR(PHONE,10,4)

PHONE의 10(열번째 문자부터) ~ 4(네번째 문자까지) 문자를 출력

5678

2. 정규식을 사용해 주소를 시도구시읍면동리 주소 컬럼 나누기

SELECT REGEXP_SUBSTR(ADDR, '[^시도]+', 1, 1)||DECODE(REGEXP_INSTR(ADDR,'[도]'),'0','시','도') ADDR1 -- 서울 경기 || 도 인지 시 인지 구분
     , TRIM(REGEXP_SUBSTR(ADDR,' ([가-힝]*[구])|( ([가-힝]*[시]))',1,1)) ADDR2 -- 구인지 시인지 띄어쓰기 다음 문자열보고 판단 후 TRIM으로 공백 제거
     , TRIM(REGEXP_SUBSTR(ADDR,' ([가-힝]*[동리])',1,1)) ADDR3 -- 동인지 리인지 띄어쓰기 다음 문자열보고 판단 후 TRIM으로 공백 제거
     , TRIM(REGEXP_SUBSTR(ADDR, '([^동리]+)',1,2)) ADDR4
  FROM (
        SELECT REGEXP_REPLACE('서울시강남구역삼동698-8','([시도구읍면동리])','\1 ') ADDR  FROM DUAL  -- 띄어쓰기 된 상태로 서울시 강남구 역삼동 698-8 출력
       )

DECODE는 컬럼명의 조건을 줘 참인지 거짓인지 판단해 출력함.

DECODE(컬럼명,'조건',참,거짓)

DECODE(SERIAL_NM,'10','삼성','LG')

DECODE(컬럼명,'조건1','값1','조건2','값2','조건3','값3')

DECODE(SERIAL_NM,'10','삼성','20','LG','30','APPLE')

DECODE 사용 ADDR의 도 값이 0과 같다면 시, ADDR의 도 값이 0과 다르다면 도

한마디로 경기. 강원, 제주, 경상, 전라 .. 도가 들어오면 도에 들어간 INSTR 값은 3이다. 3이면 도

김포, 수원, 안양, 의정부, 화성 시 일경우 도라는 값이 없어 0과 같으므로 시로 출력

TRIM은 공백제거로 사용함

3. 정규식을 사용해 숫자를 문자로  변환, 문자를 숫자로 변환

SELECT TRANSLATE(
        REGEXP_REPLACE (
          REGEXP_REPLACE (
             REGEXP_REPLACE (LPAD (REGEXP_REPLACE(TXT,'(,)',''), 16, 0),'([0-9])([0-9])([0-9])([0-9])','\1천\2백\3십\4 ')
                           ,'(.*) (.*) (.*) (.*) ','\1조\2억\3만\4')
                        ,'0천0백0십0.|0[천백십]','')
                       ,'1234567890','일이삼사오육칠팔구') AS Num2String
  FROM (
        SELECT '100000000012000' AS TXT FROM DUAL 
       )
       
             
     SELECT TO_NUMBER (
                 DECODE (INSTR (JO, '조'),'', '0000',
                           DECODE (INSTR (JO, '천'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (JO,'(.[천])',1,1),'[가-힝]',''))
                        || DECODE (INSTR (JO, '백'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (JO,'(.[백])',1,1),'[가-힝]',''))
                        || DECODE (INSTR (JO, '십'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (JO,'(.[십])',1,1),'[가-힝]',''))
                        || DECODE (REGEXP_REPLACE (JO, '(.[천백십])', ''), '조', '0',REGEXP_REPLACE (REGEXP_REPLACE (JO, '(.[천백십])', ''),'[조]',''))
                        )
              || DECODE (INSTR (UK, '억'),'', '0000',
                           DECODE (INSTR (UK, '천'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (UK,'(.[천])',1,1),'[가-힝]',''))
                        || DECODE (INSTR (UK, '백'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (UK,'(.[백])',1,1),'[가-힝]',''))
                        || DECODE (INSTR (UK, '십'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (UK,'(.[십])',1,1),'[가-힝]',''))
                        || DECODE (REGEXP_REPLACE (UK, '(.[천백십])', ''),'억', '0',REGEXP_REPLACE (REGEXP_REPLACE (UK, '(.[천백십])', ''),'[억]',''))
                        )
              || DECODE (INSTR (MAN, '만'),'', '0000',
                           DECODE (INSTR (MAN, '천'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (MAN,'(.[천])',1,1),'[가-힝]',''))
                        || DECODE (INSTR (MAN, '백'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (MAN,'(.[백])',1,1),'[가-힝]',''))
                        || DECODE (INSTR (MAN, '십'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (MAN,'(.[십])',1,1),'[가-힝]',''))
                        || DECODE (REGEXP_REPLACE (MAN, '(.[천백십])', ''),'만', '0',REGEXP_REPLACE (REGEXP_REPLACE (MAN, '(.[천백십])', ''),'[만]',''))
                        )
              || DECODE (INSTR (ONE, '천'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (ONE,'(.[천])',1,1),'[가-힝]',''))
              || DECODE (INSTR (ONE, '백'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (ONE,'(.[백])',1,1),'[가-힝]',''))
              || DECODE (INSTR (ONE, '십'),'0', '0',REGEXP_REPLACE (REGEXP_SUBSTR (ONE,'(.[십])',1,1),'[가-힝]',''))
              || DECODE (REGEXP_REPLACE (ONE, '(.[천백십])', ''),'', '0',REGEXP_REPLACE (ONE, '(.[천백십])', ''))
              ) AS NUM
       FROM (      
               SELECT REGEXP_SUBSTR(TEXT,'(.*[조])', 1, 1) AS JO
                   , TRIM(REGEXP_REPLACE(REGEXP_SUBSTR(TEXT,'(.*[억])', 1, 1),'(.*[조])','')) AS UK
                   , TRIM(REGEXP_REPLACE(REGEXP_SUBSTR(TEXT,'(.*[만])', 1, 1),'(.*[조])|(.*[억])','')) AS MAN
                   , TRIM(REGEXP_REPLACE(REGEXP_REPLACE(TEXT,' (.*)','\1'),'(.*[조])|(.*[억])|(.*[만])','')) AS ONE
                FROM (
                      SELECT REGEXP_REPLACE(TRANSLATE('일백조일만이천','일이삼사오육칠팔구','123456789'),'([조억만])','\1 ') AS TEXT FROM DUAL
                     )
             )

위와 같은 방식으로 SUBSTR, REPLACE, INSTR, DECODE, TRIM 을 적절히 활용하여 숫자를 문자로 변환, 문자를 숫자로 변환하는 쿼리를 작성할 수 있다.