[Oracle] 오라클 SQL에서 정규식 사용하여 문자열 잘라내기(10g 이상)

in #kr-dev7 years ago

SQL에는 스트링 문자를 잘라내는 SUBSTR이라는 함수가 있습니다.
해당 함수를 잘 활용하면 스트링 처리에 대해서 정말 많은 도움을 받을 수 있습니다.

하지만 특정한 규칙을 갖고 있는 스트링인 경우에는 처리에 제약이 많이 있습니다.
예를들어 아래와 같은 레벨을 갖고 있는 컬럼의 데이타인 경우 각각 레벨을 분리하는 처리

대카테고리>중카테고리>소카테고리 ==> 이런 값들을 '>'으로 분리해서 각각 참조가 필요한 경우에는
INSTR로 '>'의 위치를 찾고 해당 위치를 기준으로 문자열을 잘라내야하는 반복처리가 필요합니다.
굉장히 비효율적이지요

해당기능은 오라클 10g이상에서 쉽게 정규식으로 스트링을 처리할 수 있습니다.

REGEXP_SUBSTR

  • SUBSTR 함수의 기능을 확장하였습니다. 주어진 문자열을 대상으로 정규 표현식 패턴을 수행하여, 일치하는 하위 문자열을 반환합니다.
  • 문법 : REGEXP_SUBSTR(srcstr, pattern, [,position[,occurrence[,match_option]]])
  • srcstr : 찾아야 하는 문자로 소스문자열 데이터 타입은 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB임
  • pattern : 512바이트까지의 정규표현식으로 데이터타입이 source_char과 다르면 pattern의 데이터타입을 source_char과 같게 변환
  • position : Oracle이 문자열에서 특정 문자를 어디에서 찾아야 하는지 위치를 나타냄. 기본으로 1로 설정되어 있으므로, 문자열의 처음부터 검색을 시작.
  • occurrence : 검색하고자 하는 문자열에서 특정 문자의 발생 횟수. 기본으로 1로 설정되어 있으며, 이는 Oracle이 문자열에서 첫번째 발생 pattern을 찾는다는 의미.
  • match_option : match를 시도할 때의 옵션

사용 예

  • 이메일 주소 처리
SELECT REGEXP_SUBSTR (email,
                      '[^@]+',
                      1,
                      1)
          AS "ID",
       REGEXP_SUBSTR (email,
                      '[^@]+',
                      1,
                      2)
          AS "MailAddr"
  FROM (SELECT 'hanaro@leesunsin.com' email FROM DUAL);



  • 아이피 주소 추출 처리
SELECT ROWNUM rn
     , ip
     , LPAD(REGEXP_SUBSTR(ip, '[^.]+', 1, 1), 3, '0') ip_1
     , LPAD(REGEXP_SUBSTR(ip, '[^.]+', 1, 2), 3, '0') ip_2
     , LPAD(REGEXP_SUBSTR(ip, '[^.]+', 1, 3), 3, '0') ip_3
     , LPAD(REGEXP_SUBSTR(ip, '[^.]+', 1, 4), 3, '0') ip_4
  FROM (SELECT '10.100.10.1' ip FROM dual
        UNION ALL SELECT '10.100.1.10' FROM dual
        UNION ALL SELECT '100.10.1.10' FROM dual
        UNION ALL SELECT '100.10.1.20' FROM dual
        UNION ALL SELECT '2.10.1.140'  FROM dual)t
 ORDER BY ip_1, ip_2, ip_3, ip_4;



저같은 경우에 REGEXP_SUBSTR를 굉장히 유용하게 사용하고 있습니다.
모르시는 분들이 많을것 같아 도움되시라고 이렇게 정리해서 올립니다.

Sort:  

아 SQL 쿼리에도 정규식을 쓸수 있군요 몰랐습니다. 좋은것 알고 갑니다.

좋게 봐주셔서 감사합니다~

Cheer Up!

  • from Clean STEEM activity supporter

thank you~

Resteemed your article. This article was resteemed because you are part of the New Steemians project. You can learn more about it here: https://steemit.com/introduceyourself/@gaman/new-steemians-project-launch

thank you~~~