Oracle - INSTR , SUBSTR , REGEXP_SUBSTR

SUBSTR , INSTR 이란?

문자열의 특정 구분자를 자르기위해서 SUBSTR 과 INSTR 함수를 사용한다. 

REGEXP_SUBSTR 이란?

문자열의 특정 구분자를 자르기위해서 REGEXP_SUBSTR 함수를 사용한다. ( 오라클 10g 부터 사용가능 )

 

실습 환경

Windows 10

Oracle 11g Express Edition Release 11

Oracle SQL Developer 

Database ( hr ) - hr 테이블은 오라클에서 지원하는 테이블입니다.

---

SUBSTR , INSTR 함수 사용법

SUBSTR ("문자열" , "자를시작위치" , INSTR("문자열" , "구분자"))

 

SUBSTR , INSTR 예제 1

SELECT phone_number ,
       SUBSTR(phone_number , 1 , INSTR(phone_number , '.')) AS FIRST_NUMBER , 
       SUBSTR(phone_number , 5 , INSTR(phone_number , '.')) AS MID_NUMBER ,
       SUBSTR(phone_number , 9 , INSTR(phone_number , '.')) AS LAST_NUMBER 
FROM employees;

 

SUBSTR , INSTR 예제 2

SELECT phone_number ,
       SUBSTR(phone_number , 1 , INSTR(phone_number , '.') - 1) AS FIRST_NUMBER , 
       SUBSTR(phone_number , 5 , INSTR(phone_number , '.') - 1) AS MID_NUMBER ,
       SUBSTR(phone_number , 9 , INSTR(phone_number , '.')) AS LAST_NUMBER 
FROM employees;

 

SUBSTR , INSTR 예제 3

SELECT phone_number ,
       SUBSTR(phone_number , 1 , INSTR(phone_number , '.') + 2) AS FIRST_NUMBER , 
       SUBSTR(phone_number , 5 , INSTR(phone_number , '.') + 3) AS MID_NUMBER ,
       SUBSTR(phone_number , 9 , INSTR(phone_number , '.') ) AS LAST_NUMBER 
FROM employees;

 

REGEXP_SUBSTR 함수 사용법

REGEXP_SUBSTR ("문자열" , "구분자(패턴)" , "구분자찾을위치" , "구분자찾은순서")

 

REGEXP_SUBSTR예제 

SELECT phone_number,
       REGEXP_SUBSTR(phone_number , '[^.]+' , 1 , 1 )AS FIRST_NUMBER , 
       REGEXP_SUBSTR(phone_number , '[^.]+' , 1 , 2 )AS MID_NUMBER , 
       REGEXP_SUBSTR(phone_number , '[^.]+' , 1 , 3 )AS LAST_NUMBER 
FROM employees;