[ORACLE] REGEXP_SUBSTR 사용법 및 예제

이번시간에는 오라클 정규식 표현에 대해서 알아보도록 하겠습니다.

정규식 : |

--OR(|)구문
SELECT REGEXP_SUBSTR('ABD','A(B|C)D') AS C2 FROM DUAL;--ABD
SELECT REGEXP_SUBSTR('ACD','A(B|C)D') AS C2 FROM DUAL;--ACD
SELECT REGEXP_SUBSTR('AB','A(B|C)D') AS C2 FROM DUAL;--없음

SELECT REGEXP_SUBSTR('ABD','A(B|C)D') AS C2 FROM DUAL;--ABD
SELECT REGEXP_SUBSTR('ACD','A(B|C)D') AS C2 FROM DUAL;--ACD
SELECT REGEXP_SUBSTR('AB','A(B|C)D') AS C2 FROM DUAL;--없음
SELECT REGEXP_SUBSTR('AC','A(B|C)D') AS C2 FROM DUAL;--없음
SELECT REGEXP_SUBSTR('AD','A(B|C)D') AS C2 FROM DUAL;--없음
SELECT REGEXP_SUBSTR('ABC','A(B|C)D') AS C2 FROM DUAL;--없음

정규식 : . 과 +

--마침표(.)는 모든 문자
--더하기(+)는 1회 또는 그 이상의 횟수로 일치함
SELECT REGEXP_SUBSTR('ABC','A.+') AS C2 FROM DUAL;--ABC
SELECT REGEXP_SUBSTR('ABCDEF','A.+') AS C2 FROM DUAL;--ABCDEF
SELECT REGEXP_SUBSTR('ABC','A.') AS C2 FROM DUAL;--AB
SELECT REGEXP_SUBSTR('ABCDEF','A.') AS C2 FROM DUAL;--AB

정규식 : ?

--?는 1회 일치
SELECT REGEXP_SUBSTR('AB','A.?') AS C2 FROM DUAL;--AB
SELECT REGEXP_SUBSTR('ABCD','A.?') AS C2 FROM DUAL;--AB

정규식 : [^,]+

--문자열을 콤마로 나눈다
SELECT REGEXP_SUBSTR('ABCD,EFGH','[^,]+',1,1) AS C2 FROM DUAL;--ABCD
SELECT REGEXP_SUBSTR('ABCD,EFGH','[^,]+',2,1) AS C2 FROM DUAL;--BCD
SELECT REGEXP_SUBSTR('ABCD,EFGH','[^,]+',3,1) AS C2 FROM DUAL;--CD
SELECT REGEXP_SUBSTR('ABCD,EFGH','[^,]+',4,1) AS C2 FROM DUAL;--D

--이거 3개는 왜 결과가 같은건지 모르겠네..
SELECT REGEXP_SUBSTR('ABCD,EFGH','[^,]+',1,2) AS C2 FROM DUAL;--EFGH
SELECT REGEXP_SUBSTR('ABCD,EFGH','[^,]+',2,2) AS C2 FROM DUAL;--EFGH
SELECT REGEXP_SUBSTR('ABCD,EFGH','[^,]+',3,2) AS C2 FROM DUAL;--EFGH