REGEXP_SUBSTR

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

이메일형식에서 아이디와 메일도메인 부분을 분리하여 조회한다.

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    

이메일 형식 조회

컬럼에 이메일 형식 제약조건 만들기

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

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      

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      

페이지정보에서 첫페이지를 찾아서 알려준다.

SELECT SOURCE, REPLACE(REGEXP_SUBSTR(SOURCE,'p[[:digit:]]{1,3}'),'p','') PAGE
FROM QUESTION WHERE ROWNUM < 10;

결과값

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

전체 주소에서 시도,등의 문자열을 나누어 보여준다.

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 ;

결과값

숫자를 기준으로 나눈다

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;

결과값

A1  A A3  
--- - -----
147 1 36126

공백으로 나눈다

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;

결과값

TIME                       YMD              CONVERT_TIME       
------------------------   ---------------  -------------------
2016. 1. 29. 오후 11:00:53  2016.1.29.       2016-01-29 00:00:00

전화번호 구분자(-) 로 전화번호를 나눈다.

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;

결과

STR STR2 STR3 LAST_STR
--- ---- ---- ----
010 1234 5678 5678

구분자없이 뒷자리 4자리만 가져오기 위해서는 regexp_substr 보다 substr 이 더 효과적이다.

WITH cust AS (
  SELECT '010-1234-5678' AS phone FROM dual
)
SELECT substr(phone,-4,4) AS str
FROM cust;  

숫자/문자로만 이루어진 첫단어

SELECT REGEXP_SUBSTR('first-team second-team third-team', '[[:alnum:]]*') FROM dual;

first

스페이스가 있기 전까지의 첫단어

SELECT REGEXP_SUBSTR('first-team second-team third-team', '[^ ]+') FROM dual;

first-team