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 '[email protected]' 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를 굉장히 유용하게 사용하고 있습니다.
모르시는 분들이 많을것 같아 도움되시라고 이렇게 정리해서 올립니다.