adminschool.net wiki

이 문서는 읽기 전용입니다. 원본을 볼 수는 있지만 바꿀 수는 없습니다. 문제가 있다고 생각하면 관리자에게 문의하세요.
====== REGEXP_SUBSTR ======

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

===== 이메일 아이디 분리 =====
  - 이메일형식에서 아이디와 메일도메인 부분을 분리하여 조회한다. <code>
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
;
</code> <code>
EMAIL                    ID                       MailAddr               
------------------------ ------------------------ ------------------------
starlits@adminschool.net starlits                 adminschool.net    
</code>
  - [[dbms:oracle:devel:regexp:regexp_like#이메일_형식_체크 | 이메일 형식 조회 ]]
  - [[dbms:oracle:devel:regexp:constraint | 컬럼에 이메일 형식 제약조건 만들기 ]]
===== 숫자/문자 분리 =====

==== 방법 1 ====

  - STUDYUNITCODE를 숫자와 문자를 분리하여 나타낸다. (맨 앞에 0은 삭제한다.) <code sql>
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
</code> <code>
STUDYUNITCODE	TAPE	SIDE

01A	        1	A
10B	       10	B
12A      	12	A
02B     	 2	B
23A     	23	A
30B     	30	B
</code>

==== 방법 2 ====
  - REGEXP_REPLACE 를 이용하여 숫자를 제외시킨다. <code>
with t as (
  select '01AB' tape from dual
  union all
  select '01BA' tape from dual
)
select regexp_replace(tape, '[[:digit:]]','')
from t;
</code> <code>
TAPE    SIDE
--------------
01AB    AB                                                                              
01BA    BA      
</code>
  - REGEXP_REPLACE 를 이용하여 문자를 제외시킨다. <code>
with t as (
  select '01AB' tape from dual
  union all
  select '01BA' tape from dual
)
select regexp_replace(tape, '[^[:digit:]]','')
from t;
</code><code>
TAPE    SIDE
--------------
01AB    01                                                                              
01BA    01      
</code>
===== 페이지정보 찾기 =====

  - 페이지정보에서 첫페이지를 찾아서 알려준다. <code sql>
SELECT SOURCE, REPLACE(REGEXP_SUBSTR(SOURCE,'p[[:digit:]]{1,3}'),'p','') PAGE
FROM QUESTION WHERE ROWNUM < 10;
</code>
  - 결과값 <code>
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
</code>

===== 주소문자열 자르기 =====
  - 전체 주소에서 시도,등의 문자열을 나누어 보여준다. <code sql>
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 ;
</code>
  - 결과값 

^ 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 | 

===== 구분자로 나누기 =====
  - 숫자를 기준으로 나눈다 <code sql>
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;
</code>
  - 결과값 <code>
A1  A A3  
--- - -----
147 1 36126
</code>

  - 공백으로 나눈다 <code sql>
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;
</code>
  - 결과값 <code>
TIME                       YMD              CONVERT_TIME       
------------------------   ---------------  -------------------
2016. 1. 29. 오후 11:00:53  2016.1.29.       2016-01-29 00:00:00
</code>

===== 전화번호 나누기 =====
  - 전화번호 구분자(-) 로 전화번호를 나눈다. <code sql>
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;
</code>
  - 결과 <code>
STR STR2 STR3 LAST_STR
--- ---- ---- ----
010 1234 5678 5678
</code>

==== 뒷자리 4자리만 가져오기 ====
  - 구분자없이 뒷자리 4자리만 가져오기 위해서는 regexp_substr 보다 substr 이 더 효과적이다. <code sql>
with cust as (
  select '010-1234-5678' as phone from dual
)
select substr(phone,-4,4) as str
from cust;  
</code>

===== 첫단어 가져오기 =====
  - 숫자/문자로만 이루어진 첫단어 <code sql>
SELECT REGEXP_SUBSTR('first-team second-team third-team', '[[:alnum:]]*') FROM dual;
</code> <code>first</code>
  - 스페이스가 있기 전까지의 첫단어 <code sql>
SELECT REGEXP_SUBSTR('first-team second-team third-team', '[^ ]+') FROM dual;
</code><code>first-team</code>
adminschool.net wiki

사용자 도구

사이트 도구

문서 도구
