오라클 REGEXP 함수

 

REGEXP_LIKE(str, pattern, [, opt])	패턴을 사용하여 문자열을 검증(where절 ) [opt] c: 대소문자 구분, i: 대소문자 구분 안함, m: 다중 행 검색
REGEXP_SUBSTR(str, pattern [, pos[,occur[,opt]]])	패턴에 일치하는 하위 문자열 반환 pos: 시작위치, occur: 몇번째
REGEXP_REPLACE(str, pattern, [,replace [,pos [,occur[,opt]]])	패턴에 일치하는 문자열 변환 (패턴에 맞지 않는 경우 원본 반환) replace: 바꿀 문자열
REGEXP_INSTR(strk pattern, [, pos [,occur [,retopt [,opt]]]])	패턴에 일치하는 위치 반환 [retopt] 0: 시작위치, 1: 일치하는 마지막 위치

 

패턴	의미	패턴	의미
^	시작 문자열	|	또는
$	종결 문자열	\	제외(패턴문자)
?	앞의 문자/식 0, 1	[]	문자의 범위 [0-9]
+	1개 이상	{}	반복 {n},{n,},{n,m}
*	0개 이상	()	식(작은 그룹)
.	어떠한(ANY) 문자	\n, \t ...	개해, 탭 등
\d, \D	[0-9], [^0-9]	\w, \W	문자 및 숫자 [0-9, a-z, A-Z_]

 

---

REGEXP_LIKE

 

- 회원 중에 성이 '김'이고, 성 다음에 '성' 또는 '형'이 있는 회원 검색

select mem_id 회원id, mem_name 회원명 from member
where regexp_like(mem_name,'김(성|형)');

 

 

- 상품 이름 중에 '삼성'이라는 말로 시작하고, 숫자 두개가 같이 있는 상품의 상품코드, 상품명, 판매가 검색

select prod_id 상품id, prod_name 상품명 from prod 
where regexp_like(prod_name, '^삼성.*\d\d');

 

삼성.*칼라 : '삼성'글자(.)가 0또는 1개 이상

삼성.+칼라: '삼성'글자(.)가 1개 이상

 

 

 

---

 

REGEXP_SUBSTR

 

-공백이 아닌 데이터가 1개 이상 연결되는 문자열 중 첫번째부터 시작해서 세번째 문자열 반환

select regexp_substr('Java Flex Oracle', '[^ ]+',1,3) from dual;

 

 

- 회원테이블에서 이메일주소를 근거로 이메일아이디, 이메일 서버로 구분하여 검색

select mem_name, mem_mail, 
    regexp_substr(mem_mail,'[^@]+')이메일아이디,
    regexp_substr(mem_mail, '[^@]+',1,2) 이메일서버 
from member;

 

 

- 'c-01-02' 문자 나누기

regexp_substr(대상문자, 패턴, 시작위치(최소값1),매칭순번)

 

select regexp_substr('c-01-02','[^-]+',1,1) from dual; -- 결과 = c
select regexp_substr('c-01-02','[^-]+',1,2) from dual; -- 결과 = 01
select regexp_substr('c-01-02','[^-]+',1,3) from dual; -- 결과 = 02

 

[] 안의 ^는 not을 의미

^문자가 대괄호 밖에서 사용되면 문자열의 시작을 의미

+는 문자패턴이 1개 이상 연결될 때를 나타냄(위 예제에서는 01, 02등 2개 이상 나타내기 위함)

+시작위치 & 매칭 순번

앞의 대상문자와 패턴에 의해 나누어진 문자들을 몇번째 인덱스에서 시작하여 몇번째의 나누어진 문자를 가져올 것인지에 대한 파라미터

즉, 위 예제에서 1,2는 C//01//02의 나누어진 문자중 1번째 인덱스부터 시작하는 두번째 문자를 가져오라는 뜻

---

 

REGEXP_REPLACE

select regexp_replace('java flex oracle','[^ ]+','C++') from dual;

 

select regexp_replace('java flex oracle','[^ ]+','C++',1,2) from dual;

 

 

 

--대소문자 구분 x
select replace('oracle database 23ai', 'oracle','******') replace,
    regexp_replace('oracle database 23ai','oracle','******',1,0,'i')
    regexp_replace from dual;
 
-- 대소문자 구분
select replace('oracle database 23ai', 'oracle','******') replace,
  	regexp_replace('oracle database 23ai','oracle','******')
    regexp_replace from dual;

i는 대소문자 구분 x

 

 

---

 

 

REGEXP_INSTR

 

- a나 e가 첫번째 오는 위치 

select regexp_instr('java flex oracle','[ae]') result from dual;

 

- a나 e가 대소문자구분없이(i) 첫번째 오는 위치(a)로부터 첫번째 오는 위치(a), -- 2

 a나 e가 대소문자구분없이 세번째(v)부터 시작후 두번째 오는 위치(e)의 다음문자(x) 위치 --9

select regexp_instr('java flex oracle','[ae]',1,1,0,'i') result1,
        regexp_instr('java flex oracle','[ae]',3,2,1,'i') result2 from dual;