목차

REGEXP_SUBSTR

정의

  1. 오라클 정규표현식의 일종.

이메일 아이디 분리

  1. 이메일형식에서 아이디와 메일도메인 부분을 분리하여 조회한다.
    with t as (
      select 'starlits@adminschool.net' as email from dual
    )
    SELECT email
      ,REGEXP_SUBSTR(email, '[^@]+', 1, 1) AS "ID"
      ,REGEXP_SUBSTR(email, '[^@]+', 1, 2) AS "MailAddr"
    FROM t
    ;
    EMAIL                    ID                       MailAddr               
    ------------------------ ------------------------ ------------------------
    starlits@adminschool.net starlits                 adminschool.net    

숫자/문자 분리

방법 1

  1. STUDYUNITCODE를 숫자와 문자를 분리하여 나타낸다. (맨 앞에 0은 삭제한다.)
    SELECT  
        STUDYUNITCODE,
        REGEXP_SUBSTR(REGEXP_REPLACE(STUDYUNITCODE,'^0',''),'[[:digit:]]{1,2}') TAPE,
        REGEXP_SUBSTR(STUDYUNITCODE,'[[:alpha:]]{1}') SIDE
    FROM TB_STUDYUNIT 
    ORDER BY KWON,STUDYUNITCODE
    STUDYUNITCODE	TAPE	SIDE
    
    01A	        1	A
    10B	       10	B
    12A      	12	A
    02B     	 2	B
    23A     	23	A
    30B     	30	B

방법 2

  1. REGEXP_REPLACE 를 이용하여 숫자를 제외시킨다.
    with t as (
      select '01AB' tape from dual
      union all
      select '01BA' tape from dual
    )
    select regexp_replace(tape, '[[:digit:]]','')
    from t;
    TAPE    SIDE
    --------------
    01AB    AB                                                                              
    01BA    BA      
  2. REGEXP_REPLACE 를 이용하여 문자를 제외시킨다.
    with t as (
      select '01AB' tape from dual
      union all
      select '01BA' tape from dual
    )
    select regexp_replace(tape, '[^[:digit:]]','')
    from t;
    TAPE    SIDE
    --------------
    01AB    01                                                                              
    01BA    01      

페이지정보 찾기

  1. 페이지정보에서 첫페이지를 찾아서 알려준다.
    SELECT SOURCE, REPLACE(REGEXP_SUBSTR(SOURCE,'p[[:digit:]]{1,3}'),'p','') PAGE
    FROM QUESTION WHERE ROWNUM < 10;
  2. 결과값
    SOURCE	                 PAGE
    
    b1: p24	                  24
    b1: p19, 24, 32, 46	    19
    b3: p5	                   5
    b3: p29	                  29
    b3: p10-11	           10
    b2: p22	                  22
    b1: p20 b2: p15 b4: p40  20
    b2: p8-9, 30	            8
    b3: p16-17	           16

주소문자열 자르기

  1. 전체 주소에서 시도,등의 문자열을 나누어 보여준다.
    SELECT 
         ADDRESS
        ,REGEXP_SUBSTR(ADDRESS,'[^ ]+') AS SIDO
        ,REPLACE(REGEXP_SUBSTR(ADDRESS,' +[^ ]+'),' ','') AS GUGUN
        ,REPLACE(ADDRESS,REGEXP_SUBSTR(ADDRESS,'[^ ]+')||REGEXP_SUBSTR(ADDRESS,' +[^ ]+')||' ','') AS etc
        ,REGEXP_SUBSTR(ADDRESS,'[^ ]+(.)$')    
    FROM DV_ADDRESS_TEMP ;
  2. 결과값
address sido gugun remain etc
충남 청양군 청양읍 읍내리 175-21 충남 청양군 청양읍 읍내리 175-21 175-21
충남 청양군 읍내리 209-3 충남 청양군 읍내리 209-3 209-3
충남 태안군 태안읍 동문리 529-2 충남 태안군 태안읍 동문리 529-2 529-2
충남 태안군 태안읍 동문리 879-3 충남 태안군 태안읍 동문리 879-3 879-3

구분자로 나누기

  1. 숫자를 기준으로 나눈다
    WITH tt AS (
      SELECT '147[1]36126[2]37980[3]2047627[4]1207745[5]' AS str FROM dual
    )
    SELECT 
       REGEXP_SUBSTR(str,'[0-9]+',1,1) AS a1
      ,REGEXP_SUBSTR(str,'[0-9]+',1,2) AS a2
      ,REGEXP_SUBSTR(str,'[0-9]+',1,3) AS a3  
    FROM tt;
  2. 결과값
    A1  A A3  
    --- - -----
    147 1 36126
  1. 공백으로 나눈다
    WITH v_time AS (
        SELECT '2016. 1. 29. 오후 11:00:53' AS TIME FROM dual
    )
    SELECT 
        TIME
        ,REGEXP_SUBSTR(TIME,'[^ ]+',1,1,'i') 
        || REGEXP_SUBSTR(TIME,'[^ ]+',1,2,'i') 
        || REGEXP_SUBSTR(TIME,'[^ ]+',1,3,'i') AS ymd
        ,TO_DATE(REGEXP_SUBSTR(TIME,'[^ ]+',1,1,'i') 
        || REGEXP_SUBSTR(TIME,'[^ ]+',1,2,'i') 
        || REGEXP_SUBSTR(TIME,'[^ ]+',1,3,'i'),'YYYY.fmMM.DD.') AS convert_time   
    FROM v_time;
  2. 결과값
    TIME                       YMD              CONVERT_TIME       
    ------------------------   ---------------  -------------------
    2016. 1. 29. 오후 11:00:53  2016.1.29.       2016-01-29 00:00:00

전화번호 나누기

  1. 전화번호 구분자(-) 로 전화번호를 나눈다.
    WITH cust AS (
      SELECT '010-1234-5678' AS phone FROM dual
    )
    SELECT 
    REGEXP_SUBSTR(phone, '[^-]+', 1, 1) AS str1
         , REGEXP_SUBSTR(phone, '[^-]+', 1, 2) AS str2
         , REGEXP_SUBSTR(phone, '[^-]+', 1, 3) AS str3
         , REGEXP_SUBSTR(phone, '[^-]+$', 1, 1) AS last_str 
    FROM cust;
  2. 결과
    STR STR2 STR3 LAST_STR
    --- ---- ---- ----
    010 1234 5678 5678

뒷자리 4자리만 가져오기

  1. 구분자없이 뒷자리 4자리만 가져오기 위해서는 regexp_substr 보다 substr 이 더 효과적이다.
    WITH cust AS (
      SELECT '010-1234-5678' AS phone FROM dual
    )
    SELECT substr(phone,-4,4) AS str
    FROM cust;  

첫단어 가져오기

  1. 숫자/문자로만 이루어진 첫단어
    SELECT REGEXP_SUBSTR('first-team second-team third-team', '[[:alnum:]]*') FROM dual;
    first
  2. 스페이스가 있기 전까지의 첫단어
    SELECT REGEXP_SUBSTR('first-team second-team third-team', '[^ ]+') FROM dual;
    first-team