Oracle Database 10g에 추가된 새로운 기능을 이용하여 문자 데이터의 검색, 처리 능력을 극적으로 개선할 수 있습니다.
정규 표현식(regular expression)이라 불리는 이 기능은, 텍스트 패턴을 기술하기 위한 일종의 표기법으로,
이미 오래 전부터 다양한 프로그래밍 언어와 UNIX 유틸리티를 통해 지원되어 왔습니다.

정규 표현식은 하나 또는 그 이상의 문자열과 메타문자(metacharacter)로 구성됩니다.
가장 단순한 형태의 정규 표현식은 cat과 같은 단 하나의 문자열로만 구성될 수 있습니다.
이 정규 표현식은 문자 c와 문자 a, 문자 t의 순서를 갖는 패턴 매치 문자열로 cat, location, catalog 등의 문자열과 매치됩니다.
메타문자는 정규 표현식을 구성하는 문자들을 처리하는 방법을 명시하기 위한 알고리즘을 제공합니다.
다양한 메타문자의 의미를 이해한다면, 정규 표현식이 텍스트 데이터를 비교하고 대체하는 용도로 매우 유용하게 활용될 수 있음을 금방 깨닫게 되실 것입니다.

데이터의 검증, 중복 단어의 확인, 불필요한 공백의 제거, 문자의 파싱(parsing) 등 정규 표현식의 활용 방법은 실로 다양합니다.
정규 표현식을 이용하여 전화 번호, 우편 번호, 이메일 주소, 주민등록번호, IP 주소, 파일 이름, 경로 이름 등을 검증할 수도 있습니다.
또 HTML 태그, 숫자, 날짜, 기타 특정 텍스트 데이터와 일치하는 패턴을 확인하고 다른 패턴으로 대체하는 것이 가능합니다.

오라클 10g부터 새로 추가된 기능으로 Oracle SQL REGEXP_LIKE 연산자, REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_REPLACE 함수 등이 있습니다.
이 함수와 연산자는 기존의 LIKE 연산자와 INSTR, SUBSTR, REPLACE 함수를 보완하는 효과를 제공합니다.
실제로 새로운 기능들은 기존 연산자 및 함수와 유사하지만 훨씬 강력한 패턴 매칭 환경을 구현하고 있습니다.
검색의 기준이 되는 데이터는 간단한 문자열일 수도 있고 데이터베이스 테이블의 문자 컬럼에 저장된 대량의 텍스트일 수도 있습니다.
정규 표현식을 이용하면 이전에는 생각도 못했던 유연한 방법으로 데이터를 검색, 대체, 검증할 수 있습니다.

새로운 기능을 사용해 보기기 전에, 몇 가지 메타문자의 의미를 이해해 보기로 합시다.
마침표(.)는 정규 표현식에 존재하는 모든 문자(newline 제외)와 매칭됩니다.
예를 들어 정규 표현식 a.b는 문자 a, (newline을 제외한) 임의의 단일 문자, 그리고 문자 b의 순서로 구성된 문자열과 매칭됩니다.
문자열 axb, xaybx, abba는 모두 이 정규표현식에 정의된 패턴을 포함하고 있으므로 매치가 가능합니다.
라인이 a로 시작하여 b로 끝나는, 3 개 문자로 구성된 문자열을 매칭하고자 하는 경우에는 앵커(anchor)가 사용되어야 합니다.
캐럿(^) 메타문자는 라인의 시작을, 달러($) 기호는 라인의 끝을 의미합니다 (표 1 참고).
따라서 정규 표현식 ^a.b$는 aab, abb, axb와 같은 문자열과 매칭됩니다.
LIKE 연산자에서 이와 동일한 기능을 수행하려면 a_b 패턴을 사용해야 합니다.
여기서 밑줄 기호(_)는 단문자 와일드카드를 의미합니다.

기본적으로, 정규 표현식의 개별 문자 또는 문자 리스트는 단 한 번만 매칭됩니다.
정규 표현식은 문자가 여러 번 반복 출현되는 조건을 지정하기 위한? 반복 연산자(repetition operator)를 제공합니다(“quantifier”라 부르기도 합니다).
문자 a로 시작해서 b로 끝나는 문자열 매칭을 위한 정규 표현식이 아래와 같습니다: ^a.*b$. * 메타문자는 임의의 메타문자(.)가 0 번, 한 번, 또는 여러 번 반복되는 조건에 매칭됩니다.
LIKE 연산자에서는 이와 동일한 연산자로 a%b를 지원합니다.
여기서 퍼센트(%) 기호는 임의 문자가 0 번, 한 번, 또는 여러 번 반복됨을 의미합니다.

표 2는 반복 연산자의 전체 목록을 보여 주고 있습니다.
이 표에 제시된 예를 통해 정규 표현식이 기존의 LIKE와일드카드 문자보다 훨씬 뛰어난 유연성을 제공함을 확인할 수 있습니다.
표현식에 괄호를 씌우는 경우, 서브표현식(subexpression)으로 활용됩니다.
서브표현식은 임의의 횟수만큼 반복될 수 있습니다.
예를 들어, 정규 표현식 b(an)*a는 ba, bana, banana, yourbananasplit등과 매치됩니다.

오라클의 정규 표현식은 POSIX(Portable Operating System Interface) 문자 클래스를 지원합니다(표 3 참고).
따라서 검색하는 문자의 유형을 세부적으로 정의하는 것이 가능합니다.
알파벳이 아닌 문자를 검색하는 조건을 LIKE 연산자로 작성한다면, WHERE 절이 훨씬 복잡한 형태로 구현되어야 할 것입니다.

POSIX 문자 클래스는 반드시 대괄호([])로 묶여 져야 합니다.
예를 들어, 정규 표현식 [[:lower:]]는 소문자와 매치되며 d[[:lower:]]{5}는 5 개의 연속적인 소문자와 매치됩니다.

POSIX 문자 클래스와 별도로, 개별 문자를 문자 리스트(character list)에 포함시키는 기능이 제공됩니다.
예를 들어 정규 표현식 ^ab[cd]ef$는 문자열 abcef , abdef와 매치됩니다.
여기서 c 또는 d 두 개의 문자 중 하나가 사용되고 있어야 합니다.

문자 리스트 내부에 위치하는 대부분의 메타문자는 일반 문자로 인식됩니다.
그 예외가 캐럿 (^) 기호와 하이픈 (-)기호입니다.
일부 메타문자는 문맥에 따라 다른 의미를 갖습니다.
이 때문에 정규 표현식이 무척 복잡해 보일 수도 있습니다.
캐럿 ^이 그 한 가지 예입니다.
이 기호를 문자 리스트의 첫 번째 문자로 사용되는 경우에는, 문자 리스트의 반대 조건(negation)을 의미합니다.
따라서 [^[:digit:]]은 숫자가 아닌 문자로 구성된 패턴과 매칭되는 반면 ^[[:digit:]] 은 숫자로 시작되는 패턴과 매칭됩니다.
하이픈 (-)은 영역(range)을 의미합니다.
정규 표현식 [a-m]은 a와 m 사이의 임의의 문자와 매칭됩니다.
하지만 [-afg]의 경우처럼 하이픈이 문자 리스트의 첫 번째 문자로 사용된 경우에는 실제 하이픈 문자를 의미합니다

앞에서 괄호를 사용하여 서브표현식을 구현하는 방법을 예시한 바 있습니다.
서브표현식에서는 수직 기호(|)메타문자를 사용하여 여러 개의 대체 문자를 지정할 수 있습니다.

예를 들어, 정규 표현식 t(a|e|i)n은 문자 t와 n 사이에 오는 3 개의 대체 문자를 지정하고 있습니다.
tan, ten, tin, Pakistan 등의 문자열은 매치되지만 teen, mountain, tune 등은 매치되지 않습니다.
또 정규 표현식 t(a|e|i)n을 문자 리스트 t[aei]n으로 표현할 수도 있습니다.
표 4는 이러한 메타문자들을 요약하고 있습니다.
지금까지 설명한 것 말고도 다양한 메타문자가 있지만, 여기에서는 본 문서에서 예제로 사용되는 정규 표현식을 이해할 수 있는 정도만 이해하고 넘어가기로 합니다.

REGEXP_LIKE오라클 데이터베이스에 적용 가능한 정규 표현식 기능을 제공합니다.
표 5는 REGEXP_LIKE의 문법을 보여주고 있습니다

아래 SQL 쿼리의 WHERE 절에서 사용된 REGEXP_LIKE 연산자는 정규 표현식 [^[:digit:]]을 만족하는 패턴의 ZIP 컬럼을 검색하고 있습니다.
이 조건절을 이용하여, ZIPCODE 테이블로부터 숫자가 아닌 문자를 포함하는 ZIP 컬럼이 포함된 모든 로우를 가져올 수 있습니다.

SELECT zip FROM zipcode WHERE REGEXP_LIKE(zip, '[^[:digit:]]')
 
ZIP
-----
ab123
123xy
007ab
abcxy 

이 정규 표현식은 메타문자, 좀 더 정확히 말하면 콜론과 대괄호로 묶인 POSIX 문자 클래스 digit만을 사용하고 있습니다.
[^[:digit:]]에서 두 번째로 사용된 대괄호는 문자 클래스 리스트를 묶는 용도로 사용됩니다.
앞에서 설명한 것처럼 POSIX는 문자 리스트를 구성하는 용도로만 사용되므로 이와 같은 처리가 필요합니다.

이 함수는 패턴의 시작 위치를 반환하며, 따라서 INSTR 함수와 유사한 형태로 동작합니다.
REGEXP_INSTR함수의 사옹 방법은 표 6에서 확인할 수 있습니다.
두 함수의 가장 중요한 차이는 REGEXP_INSTR를 이용하는 경우 특정 문자열이 아닌 패턴을 지정할 수 있으며,
따라서 훨씬 유연한 검색이 가능하다는 사실입니다.
다음 예에서는 REGEXP_INSTR을 사용하여 Joe Smith, 10045 Berry Lane, San Joseph, CA 91234문자열에서 5 개의 숫자로 구성된 우편 번호 패턴의 시작 부분을 반환하고 있습니다.
정규 표현식 digit{5}를 사용하는 경우 우편 번호가 아닌 집 주소 번호의 시작 위치를 얻게 됩니다
(처음으로 검색되는 5 개 연속 숫자 패턴이 10045이기 때문입니다).
따라서 $ 메타문자를 사용하여 표현식의 앵커를 라인 끝부분으로 지정해야 합니다.
이렇게 하면 집 주소 번호에 관계없이 우편 번호의 시작 위치를 얻을 수 있습니다.

SQL> SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234', '[[:digit:]]{5}$') AS rx_instr FROM dual
 
  RX_INSTR
---------- 
        45 

좀더 복잡한 패턴 작성

앞의 예의 우편 번호 패턴을 확장하여 네 가지 숫자를 포함하는 패턴을 만들어 보기로 합시다.
새로 작성된 패턴이 아래와 같습니다:

[[:digit:]]{5}(-[[:digit:]]{4})?$. 

소스 문자열이 5 개 숫자로 종료되든, 또는 “5 개 숫자 + 4 자리 우편 번호” 포맷을 갖든, 패턴의 시작 위치를 얻을 수 있습니다.

SQL> SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234', ' [[:digit:]]{5}(-[[:digit:]]{4})?$') AS starts_at FROM dual
 
 STARTS_AT
----------
        44 

위의 예에서 괄호로 묶인 서브표현식 (-[ [:digit:] ]{4})는 반복 연산자 ?로 지정된 조건에 따라 0 회 또는 1 회 반복됩니다.
다시 말하지만, 기존의 SQL 함수를 이용하여 같은 결과를 얻어 내려면 아무리 SQL 전문가라 해도 쉽지 않은 작업이 될 것입니다.
표 7은 정규 표현식을 구성하는 각 문자와 메타문자의 의미를 설명하고 있습니다.

REGEXP_SUBSTR 함수는 SUBSTR 함수와 마찬가지로 문자열의 일부를 추출합니다.
표 8은 새로운 함수의 사용법을 설명하고 있습니다.
아래 예제에서는 [^,]*, 패턴에 매치되는 문자열이 반환됩니다.
정규 표현식은 공백에 이어 사용된 쉼표를 검색하고, 쉼표가 아닌 문자가 0 회 또는 그 이상 반복되는 패턴을 검색([^,]*)한 후 마지막으로 또 다른 쉼표를 검색합니다.
이 패턴은 쉼표로 구분된 문자열(comma-separated values)과 유사한 배열을 갖습니다.

SQL> SELECT REGEXP_SUBSTR('first field, second field , third field', ', [^,]*,') FROM dual
 
REGEXP_SUBSTR('FIR
------------------
, second field   , 

특정 문자열을 다른 문자열로 대체하는 기존의 REPLACE SQL 함수의 기능을 잠시 되짚어 보겠습니다.
데이터의 텍스트에 필요 이상의 공백 기호가 존재하는 상황에서, 이를 단일 공백 기호로 대체하는 경우를 가정해 봅시다
REPLACE 함수를 사용할 때에는 대체할 공백 기호의 숫자를 정확하게 지정해야 합니다.
하지만, 필요 없는 공백의 수가 일정하리라는 보장은 없습니다.
아래 예는 Joe와 Smith사이에 3 개의 공백 기호가 존재하는 경우를 검색하고 있습니다.
REPLACE 함수의 매개변수는 두 개의 공백 기호를 하나의 공백 기호로 대체할 것을 명시하고 있습니다.
하지만 Joe와 Smith 사이에 3 개의 공백 기호가 존재하는 경우에는 여전히 필요 없는 공백이 하나 남게 됩니다.

SQL> SELECT REPLACE('Joe   Smith','  ', ' ') AS REPLACE FROM dual
 
REPLACE
---------
Joe Smith 

REGEXP_REPLACE 한층 개선된 문자열 대체 기능을 제공합니다.
그 사용법은 표 9에서 설명되고 있습니다.
아래 쿼리는 두 개 또는 그 이상의 공백 기호를 하나의 공백 기호로 대체합니다.
( )서브표현식은 하나의 공백 기호를 포함하며 {2,}의 조건에 의해 지정된 대로 2 회 또는 그 이상 반복되는 조건을 명시합니다.

SQL> SELECT REGEXP_REPLACE('Joe   Smith','( ){2,}', ' ') AS RX_REPLACE FROM dual
 
RX_REPLACE
----------
Joe Smith 

정규 표현식의 유용한 기능의 하나로 재활용을 위해 서브표현식을 저장하는 기능이 제공됩니다.
이 기능을 백레퍼런스(backreferencing)라 부릅니다(상세한 설명은 표 10 참고).
백레퍼런스를 이용하여 패턴을 새로운 위치에 맞교체하거나 반복적인 단어 또는 문자를 검색하는 등의 고급 대체 기능을 구현할 수 있습니다.
서브표현식과 일치하는 문자열은 임시 버퍼에 저장됩니다.
이 버퍼에는 왼쪽에서 오른쪽 순서로 숫자가 매겨지며 \digit 형태로 표현됩니다.
여기서 digit은 1과 9 사이의 숫자를 의미하며 각 숫자에 해당하는 서브표현식과 매치됩니다.

아래 예제는 서브익스프레션에 대한 백레퍼런스를 이용하여 Ellen Hildi Smith라는 이름을 Smith, Ellen Hildi로 변환하고 있습니다.

SQL> SELECT REGEXP_REPLACE('Ellen Hildi Smith', '(.*) (.*) (.*)', '\3, \1 \2') FROM dual
 
REGEXP_REPLACE('EL
------------------
Smith, Ellen Hildi 

위의 SQL 구문은 각각 괄호로 묶인 3 개의 서브표현식을 사용하고 있습니다.
각 서브표현식은 임의의 단일 문자와 매치되는 메타문자(.)와, 임의의 문자(newline 제외)와 0 회 또는 그 이상 매치되는 * 메타문자를 연이어 사용하고 있습니다.
각 서브표현식 사이에 사용된 공백 기호 역시 매치되어야 합니다.
괄호로 묶인 서브표현식에 의해 캡처된 값은 \digit에 의해 참조할 수 있습니다.
따라서 첫 번째 서브표현식에는 \1이, 두 번째 표현식에는\2이 할당됩니다.
이 백레퍼런스들은 함수의 마지막 매개변수 (\3, \1 \2)로 사용되어, 서브문자열을 대체하고 (쉼표와 공백을 포함하는) 포맷으로 표현하는 용도로 활용되고 있습니다.
표 11은 정규 표현식의 개별 컴포넌트에 대한 상세 정보를 설명하고 있습니다.

백레퍼런스는 값을 대체, 포맷하는 용도로 유용하게 활용되며, 서로 인접한 값을 찾기 위해 이용할 수도 있습니다.
다음 예제는 REGEP_SUBSTR 함수를 이용하여 공백 기호로 구분된 알파벳/숫자 값의 중복 사례를 검색하고 있습니다.
이 함수가 반환된 서브문자열을 통해 is 문자열이 반복되고 있음을 알 수 있습니다.

SQL> SELECT REGEXP_SUBSTR('The final test is is the implementation', '([[:alnum:]]+)([[:space:]]+)\1') AS substr FROM dual
 
SUBSTR
------
IS IS 

앞에서 예시한 예제들을 통해 정규 표현식 연산자와 함수에 매치 매개변수(match parameter)를 추가적으로 활용할 수 있음을 눈치 채셨을 것입니다.

이 매개변수는 대소문자 구분, newline 문자의 매치, 멀티라인 입력의 보존 등을 지원합니다.

Y정규 표현식은 단순 쿼리 이외에도 PL/SQL 언어처럼 SQL 연산자, 함수가 사용되는 경우라면 언제든 적용이 가능합니다.
또 정규 표현식을 이용하여 값의 검증, 생성, 추출을 위한 기능을 트리거 형태로 구현할 수 있습니다.

다음 예제는 입력된 데이터에 대해 컬럼 제약 조건을 검증하기 위해 REGEXP_LIKE 연산자를 사용하는 방법을 예시하고 있습니다.
이 쿼리는 INSERT, UPDATE 작업이 발생하는 경우 입력이 사회보장번호의 포맷과 일치하는지 확인합니다.
123-45-6789 또는 123456789과 같은 포맷으로 표현된 사회보장번호는 컬럼의 제약 조건을 만족합니다.
데이터는 3 개의 숫자로 시작하여 하이픈으로 연결된 후, 다시 두 개의 숫자, 하이픈, 마지막으로 4 개의 숫자로 표현됩니다.
또는 사회보장번호를 9 개의 연속적 숫자로 표현하는 것도 가능합니다.
수직 기호(|)는 두 가지 옵션 중 선택이 가능함을 의미합니다.

ALTER TABLE students
  ADD CONSTRAINT stud_ssn_ck CHECK
  (REGEXP_LIKE(ssn, '^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$'))

^, $. 기호는 가장 앞에 또는 가장 뒤에 오는 문자를 무시할 것을 지정하고 있습니다.
정규 표현식이 두 개 이상의 라인으로 분절되거나 표현식에 불필요한 공백 기호가 포함되지 않도록 주의하시기 바랍니다.
표 12는 위 정규 표현식 예제에서 사용된 각각의 컴포넌트에 대해 설명하고 있습니다.