====== 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
- [[dbms:oracle:devel:regexp:regexp_like#이메일_형식_체크 | 이메일 형식 조회 ]]
- [[dbms:oracle:devel:regexp:constraint | 컬럼에 이메일 형식 제약조건 만들기 ]]
===== 숫자/문자 분리 =====
==== 방법 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 ====
- 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 ;
- 결과값
^ 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 |
===== 구분자로 나누기 =====
- 숫자를 기준으로 나눈다
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자리만 가져오기 ====
- 구분자없이 뒷자리 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