🔍 정규표현식

✍ 오라클은 정규표현식(Regular Expression)을 이용하여 문자열을 다루는 함수들을 제공합니다. 정규표현식을 이용하면 문자열의 패턴을 지정하여 찾거나 대체할 수 있습니다. REGEXP(Regular Expression)함수는 오라클 10g에 추가된 함수이다.

🔍 정규표현식 함수

함수 명칭	설명
REGEXP_LIKE	조건(정규 표현)을 사용해 애매한 조건 검색을 실시합니다. 검색 대상열에는 문자 데이터타입을 사용합니다.(CHAR, VARCAHR2, CLOB, NCHAR, NVARCHAR2 및 NCLOB은 지원하지만 LONG은 지원하지 않는다.)
REGEXP_INSTR	지정한 조건(정규 표현)을 만족하는 부분의 최초의 위치(무슨 문자인지)를 돌려줍니다. 또한, 검색을 시작하는 시작 위치를 지정하는 것도 가능합니다.
REGEXP_SUBSTR	지정한 정규 표현을 만족하는 부분 문자열을 반환합니다.
REGEXP_REPLACE	지정한 정규 표현을 만족하는 부분을, 지정한 다른 문자열로 치환합니다. 복잡한 치환/검색 조작을 가능하게 합니다.

🔍 횟수 지정 메타 문자

함수 명칭	설명	설명
물음표(?)	0회 또는 1회	0번 또는 1번까지의 발생을 의미합니다. ex) colou?r은 color와 colour 둘 다 일치시킨다.
별표(*)	0회 이상	0번 이상의 발생을 의미합니다. ex) ab*c는 ac, abc, abbc, abbbc을 모두 일치시킨다.
덧셈기호(+)	1회 이상	1번 이상의 발생을 의미합니다. ab+c는 abc, abbc, abbbc를 일치시킨다. ac는 일치하지 않는다.
{n}	n회	정확히 n번만큼 일치시킨다.
{m,}	m회 이상	m번 이상만큼 일치시킨다.
{m,n}	m회 이상 n회 이하	m번 이상 n회 이하로 일치시킨다. ex) a{1,3}b은 ab, aab, aaab는 일치시키고 a와 aaaab는 일치하지 않는다.

🔍 메타 문자

함수 명칭	설명	설명
.	문자	문자 한 개와 일치시킵니다. 단일행 모드에서는 줄 바꿈 문자를 제외합니다.
[]	문자들	"["과 "]" 사이의 여러 문자들 중 하나의 문자와 일치시킵니다. "¦"를 여러 개 쓴 것과 같은 의미입니다. 예를들면 [abc]d는 ad, bd, cd를 의미합니다. 또한, "-" 기호와 함께 쓰면 범위를 지정할 수 있습니다. "[a-z]"는 a부터 z까지 중 하나, "[A-Z]"는 A부터 Z까지 중 하나 그리고 "[1-9]"는 1부터 9까지 중의 하나를 의미합니다.
[^]	부정	해당 문자들을 포함하지 않는 문자들을 찾습니다. 예를 들면 [^abc]d는 ad, bd, cd는 포함하지 않고 ed, fd등은 포함합니다.[^a-z]는 알파벳 소문자로 시작하지 않는 모든 문자를 의미합니다.
^	처음	문자열이나 행의 처음을 의미합니다.
$	끝	문자열이나 행의 끝을 의미합니다.
()	그룹 묶기	여러 식을 하나로 묶어 하위 식을 정의합니다. "abc¦adc"는 "a(b¦d)c"와 같은 의미를 가집니다.
\n	n번째 패턴	일치하는 패턴들 중에서 n번째를 선택합니다. n은 1에서 9 중 하나가 올 수 있습니다.
\w	"_"와 영숫자	"를 포함한 영문자와 숫자를 일치시킵니다. \w는 [a-zA-Z0-9]와 같습니다.
\W	\w 반대	"_"와 영문자 그리고 숫자를 제외한 다른 문자열들을 일치 시킵니다.
\s	공백	공백 문자(carriage return, newline, vertical tab, form feed 등)를 의미합니다. [ \t\r\n\v\f]와 같습니다.
\S	공백 제외	공백을 제외한 어떤 것이든 일치시킵니다.
\d	숫자	숫자를 의미합니다.
\D	숫자 제외	숫자가 아닌 항목과 일치시킵니다.

🔍 문자 클래스

문자 클래스	설명
[:alpha:]	알파벳 문자를 의미합니다. [A-Za-z]와 같습니다.
[:lower:]	소문자 알파벳 문자를 의미합니다. [a-z]와 같습니다.
[:upper:]	대문자 알파벳 문자를 의미합니다. [A-Z]와 같습니다.
[:digit:]	숫자를 의미합니다. [0-9]와 같습니다.
[:xdigit:]	16진수 숫자에 사용되는 문자들을 의미합니다. [A-Fa-f0-9]와 같습니다.
[:alnum:]	알파벳/숫자를 의미합니다. [A-Za-z0-9]와 같습니다.
[:space:]	출력되지 않는 공백 문자(carriage return, newline, vertical tab, form feed 등)를 의미합니다. [\t\r\n\v\f]와 같습니다
[:punct:]	구두점 기호를 의미합니다. []!"#$%&'()*+,./:;<=>?@\^_`{
[:cntrl:]	(출력되지 않는) 컨트롤 문자를 의미합니다. 아스키코드를 이용하면 [\x00-\x1F\x7F]으로 표현할 수 있습니다.
[:print:]	출력 가능한 문자(공백 포함)를 의미합니다. [:graph:]로 사용될 수 도 있습니다. 아스키코드를 이용하면[\x20-\x7E]으로 표현할 수 있습니다.

🔍 REGEXP_LIKE 함수

-- 테스트 테이블 생성
CREATE TABLE test_regexp(col1 varchar2(10));
  
-- 테이블 생성 확인
SELECT * FROM test_regexp;

INSERT INTO test_regexp VALUES('ABCDE01234');
INSERT INTO test_regexp VALUES('01234ABCDE');
INSERT INTO test_regexp VALUES('abcde01234');
INSERT INTO test_regexp VALUES('01234abcde');
INSERT INTO test_regexp VALUES('1-234-5678');
INSERT INTO test_regexp VALUES('243-567890');

COMMIT;

-- 데이터 확인 
SELECT * FROM test_regexp;

✍ REGEXP_LIKE 함수를 사용하기 위해 테이블 생성과 값을 삽입하였습니다.

-- REGEXP_LIKE 함수
SELECT * FROM test_regexp
WHERE REGEXP_LIKE(col1, '[0-9][a-z]'); -- abcde01234 출력

SELECT * FROM test_regexp
WHERE REGEXP_LIKE(col1, '[0-9]{3}-[0-9]{4}$'); -- 1-234-5678 출력

SELECT * FROM test_regexp
WHERE REGEXP_LIKE(col1, '[[:digit:]]{3}-[[:digit:]]{4}$'); -- 1-234-5678 출력(문자 클래스)

CREATE TABLE QA_MASTER(QA_NO varchar2(10));

-- 체크 제약조건(QA_NO 영문자2:숫자2:숫자4)
ALTER TABLE QA_MASTER ADD CONSTRAINT QA_NO_CHK CHECK
    (REGEXP_LIKE(QA_NO, '^([[:alpha:]]{2}-[[:digit:]]{2}-[[:digit:]]{4})$'));

INSERT INTO QA_MASTER VALUES('QA-01-0001');

-- ERROR 제약조건 위배
INSERT INTO QA_MASTER VALUES('00-01-0001');

SELECT * FROM QA_MASTER; -- QA-01-0001 출력

🔍 REGEXP_INSTR 함수

-- REGEXP_INSTR 함수 (문자 위치 반환)
INSERT INTO test_regexp VALUES('@!=)(9&%$#');

INSERT INTO test_regexp VALUES('자바3');

SELECT col1, REGEXP_INSTR(col1, '[0-9]') AS data1,
             REGEXP_INSTR(col1, '%') AS data2
FROM test_regexp;

🔍 REGEXP_SUBSTR 함수

-- REGEXP_SUBSTR 함수(부분 문자열 출력)
SELECT col1, REGEXP_SUBSTR(col1, '[C-Z]+')
FROM test_regexp; -- 대문자 C부터 Z까지 출력시킨다. 해당하지 않는 값은 NULL로 출력

🔍 REGEXP_REPLACE 함수

-- REGEXP_REPLACE 함수(부분 문자열 변환)
SELECT col1, REGEXP_REPLACE(col1, '[0-2]+', '*')
FROM test_regexp; -- 0,1,2를 덧셈기호로 '*' 하나로 문자열 변환시킨다.

🔍 REGEXP 문제

-- REGEXP 함수 실전 문제
-- EMPLOYEES 테이블의 PHONE_NUMBER 열에서 전화번호 형식(xxx.xxx.xxxx) 출력

-- 메타 문자
SELECT FIRST_NAME, PHONE_NUMBER 
FROM EMPLOYEES
WHERE REGEXP_LIKE(PHONE_NUMBER, '^[0-9]{3}.[0-9]{3}.[0-9]{4}$');

-- 문자 클래스
SELECT FIRST_NAME, PHONE_NUMBER 
FROM EMPLOYEES
WHERE REGEXP_LIKE(PHONE_NUMBER, '(^[[:digit:]]{3}.[[:digit:]]{3}.[[:digit:]]{4}$)');

-- 사원의 이름과 전화번호 형식(xxx.xxx.xxxx)을 출력하되 전화번호 끝 4자리를 *로 변환하여 출력, 끝 자리 출력
-- 메타 문자
SELECT FIRST_NAME, REGEXP_REPLACE(PHONE_NUMBER, '[0-9]{4}$', '****') AS PHONE,
                   REGEXP_SUBSTR(PHONE_NUMBER, '[0-9]{4}$') AS PHONE2
FROM EMPLOYEES
WHERE REGEXP_LIKE(PHONE_NUMBER, '(^[0-9]{3}.[0-9]{3}.[0-9]{4}$)');

-- 문자 클래스
SELECT FIRST_NAME, REGEXP_REPLACE(PHONE_NUMBER, '[[:digit:]]{4}', '****') AS PHONE,
                   REGEXP_SUBSTR(PHONE_NUMBER, '[[:digit:]]{4}') AS PHONE2
FROM EMPLOYEES
WHERE REGEXP_LIKE(PHONE_NUMBER, '(^[[:digit:]]{3}.[[:digit:]]{3}.[[:digit:]]{4}$)');

📖 reference
인프런 오라클 데이터베이스 강의