정규 표현식을 이용한 SQL 구문의 개선 (1 부에 이어 계속)

정규 표현식을 이용한 SQL 구문의 개선 (1 부에 이어 계속)

저자 – Alice Rischert

제 1 부로 돌아가기

백레퍼런스(Backreference)

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

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

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 문자열이 반복되고 있음을 알 수 있습니다.

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는 위 정규 표현식 예제에서 사용된 각각의 컴포넌트에 대해 설명하고 있습니다.

Next Steps Oracle Database 10g 페이지: /products/database/oracle10g/index.html

정규 표현식과 기존 SQL 기능의 비교

정규 표현식은 기존에 사용되던 LIKE 연산자와 INSTR, SUBSTR, and REPLACE 함수와 비교했을 때 여러 가지 이점을 제공하고 있습니다. 기존의 SQL 함수는 패턴 매칭을 전혀 지원하지 않고 있습니다. 문자의 매칭을 지원하는 컴포넌트는 LIKE연산자가 유일하며 % , _와일드카드 문자가 지원됩니다. 하지만 LIKE 연산자는 표현식의 반복, 복잡한 대체 패턴, 문자 영역, 문자 리스트, POSIX 문자 클래스 등을 지원하지 않습니다. 또 새로운 정규 표현식 함수는 반복 단어와 패턴의 맞교환(swap)을 지원한다는 장점이 있습니다. 본 문서에서 제시된 예제들이 정규 표현식의 개념과 활용 방법을 이해하는데 도움이 되었기 바랍니다.

툴킷 확장을 위한 유용한 도구

정규 표현식은 복잡한 문제의 해결을 가능하게 하는 매우 강력한 기능입니다. 정규 표현식은 기존 SQL 함수로는 흉내내기 어려운 다양한 기능을 지원합니다. 정규 표현식의 사용법이 다소 복잡해 보이기는 하지만, 그 기본적인 구성 요소만 익혀도 SQL뿐 아니라 다른 프로그래밍 언어에서도 유용한 도구로 활용하실 수 있을 것입니다. 원하는 패턴을 얻기 위해서는 여러 차례의 시행착오가 불가피할 수도 있습니다. 하지만 어느 누구도 정규 표현식의 우아함과 강력한 기능을 결코 무시할 수 없을 것입니다.

Alice Rischert (ar280@yahoo.com)는 콜럼비아 대학 컴퓨터 테크놀로지/애플리케이션 프로그램의 데이터베이스 애플리케이션 개발/설계 트랙 담당 회장입니다. 그녀는 <Oracle SQL Interactive Workbook 2nd edition(Prentice Hall, 2002)>과 조만간 출판 예정인 <Oracle SQL by Example(Prentice Hall, 2003)>의 저자이기도 합니다. Rischert 는 포춘 100대 기업에서 데이터베이스 설계, DBA, 프로젝트 리더로 15 년 간의 경력을 보유하고 있으며 오라클 데이터베이스는 버전 5부터 사용해 온 베테랑입니다.

표 1: 메타문자의 앵커(anchor) 적용

Metacharacter	설명
^	라인의 시작 부분에 표현식을 적용
$	라인의 끝부분에 표현식을 적용

표 2: 반복 연산자(Repetition Operator, Quantifier)

Quantifier	설명
*	0 회 또는 그 이상 횟수로 매치
?	0 회 또는 1 회 매치
+	1 회 또는 그 이상 횟수로 매치
{m}	정확히 m 회 매치
{m,}	최소한 m 회 매치
{m, n}	최소 m 회, 최대 n 회 매치

표 3: 사전정의된 POSIX 문자 클래스

Character Class	설명
[:alpha:]	알파벳 문자
[:lower:]	소문자 알파벳 문자
[:upper:]	대문자 알파벳 문자
[:digit:]	숫자
[:alnum:]	알파벳/숫자
[:space:]	출력되지 않는 공백 문자(예: carriage return, newline, vertical tab, form feed 등
[:punct:]	구두점 기호
[:cntrl:]	(출력되지 않는) 컨트롤 문자
[:print:]	출력 가능한 문자

표 4: 대체 매칭 및 표현식의 그룹화

Metacharacter		설명
|	Alternation	대체 문자를 구분 (그룹핑 연산자 ()와 함께 사용하기도 함)
( )	Group	반복 연산자 또는 백레퍼런스(backreference)를 위해 대체 유닛을 서브표현식으로 그룹화 ("백레퍼런스" 섹션 참고)
[char]	Character list	I문자 리스트. 문자 리스트 내부에 위치하는 대부분의 메타문자는 일반 문자로 인식됨. 그 예외가 캐럿(^) 기호와 하이픈(-) 기호

표 5: REGEXP_LIKE 연산자

문법	설명
REGEXP_LIKE(source_string, pattern [, match_parameter])	source_string은 문자 데이터타입 지원 (CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2, NCLOB 단 LONG 은 제외). pattern매개변수는 정규 표현식을 참조하는 또 다른 이름. 옵션으로 제공되는 match_parameter를 이용하여 newline 문자의 처리, 멀티라인 포맷의 유지, 대소문자 구분 등을 처리할 수 있음.

표 6: REGEXP_INSTR 함수

문법	설명
REGEXP_INSTR(source_string, pattern [, start_position [, occurrence [, return_option [, match_parameter]]]])	pattern을 검색하여 패턴의 첫 번째 위치를 반환. 필요한 경우 start_position y을 이용하여 검색을 시작할 위치를 지정할 수 있음. occurrence매개변수는 같은 패턴을 여러 번 검색하고자 하는 경우에 사용(디폴트는 1). return_option은 패턴의 시작 위치를 반환(디폴트 값은 0). 1로 설정되는 경우 매치된 패턴의 다음 문자 위치를 반환.

표 7: 5 자리 숫자 + 4 자리 Zip-Code 표현식에 대한 설명

문법	설명
	매치되어야 하는 빈 공백
[:digit:]	POSIX "numeric digit" 클래스
]	문자 리스트(character list)의 끝
{5}	문자 리스트에 대해 정확히 5 회 반복
(	서브표현식(subｅxpression)의 시작 부분
-	하이픈(-) 문자 (문자 리스트 내의 range 메타문자로 사용되지 않았음에 주의)
[	문자 리스트의 시작 부분
[:digit:]	POSIX [:digit:] 클래스
[	문자 리스트의 시작 부분
]	문자 리스트의 끝 부분
{4}	문자 리스트를 정확히 4 회 반복
)	서브표현식을 묶는 괄호 기호
?	? 반복 연산자는 그룹핑된 서브표현식을 0 회 또는 1 회 매치하여 옵션으로 4 자리 코드를 사용할 수 있게 함
$	검색 위치를 라인의 끝부분으로 지정하기 위한 앵커 메타문자

표 8: The REGEXP_SUBSTR 함수

문법	설명
REGEXP_SUBSTR(source_string, pattern [, position [, occurrence [, match_parameter]]])	REGEXP_SUBSTR 함수는 패턴애 매치되는 서브문자열을 반환.

표 9: TheREGEXP_REPLACE 함수

문법	설명
REGEXP_REPLACE(source_string, pattern [, replace_string [, position [,occurrence, [match_parameter]]]])	매치된 패턴을 지정된 replace_string으로 대체하고, 복잡한 검색/대체 작업을 가능하게 하는 함수.

표 10: 백레퍼런스 메타문자 (Backreference Metacharacter)

Metacharacter		설명
\digit	Backslash	백슬래시 뒤에 1-9 사이의 숫자를 명시하여, 괄호 처리된 N 번째 서브표현식을 참조하기 위해 사용. (참고: 백슬래시는 정규 표현식에서 다른 의미로 사용되기도 함. 문맥에 따라 Escape 문자를 의미할 수도 있음.

표 11: 패턴-스왑(Pattern-Swap) 정규 표현식의 설명

정규 표현식 아이템	설명
(	첫 번째 서브표현식의 시작
.	(newline을 제외한) 임의의 단일 문자와 매치
*	반복 연산자 - 앞의 . 메타문자와 0 회에서 n 회까지 매치
)	첫 번째 서브표현식의 마지막. 매치 결과는 \1 에 캡처됨(예제에서는 Ellen을 캡처)
	공백 기호
(	두 번째 서브표현식의 시작
.	newline을 제외한 임의의 단일 문자와 매치
*	반복 연산자 - 앞의 . 메타문자와 0 회에서 n 회까지 매치
)	두 번째 서브표현식의 마지막; 매치 결과는 \2 에 캡처됨(예제에서는 Hildi를 저장).
	공백 기호
(	세 번째 서브표현식의 시작
.	newline을 제외한 임의의 단일 문자와 매치
*	반복 연산자 - 앞의 . 메타문자와 0 회에서 n 회까지 매치
)	세 번째 서브표현식의 끝 부분. 매치 결과는 \3 에 캡처(예제에서는 Smith를 저장).

표 12: Social Security Number 정규 표현식의 설명

정규 표현식 아이템	설명
^	라인 문자의 시작(정규 표현식은 매치되기 이전의 문자를 가져 올 수 없음.)
(	서브표현식을 시작하고 | 메타문자를 이용하여 대체 목록을 제공
[	문자 리스트(character list)의 시작
[:digit:]	POSIX "numeric digit" 클래스
]	문자 리스트의 끝 부분
{3}	문자 리스트를 정확하게 3 회 반복 적용
-	하이픈
[	문자 리스트의 시작
[:digit:]	POSIX numeric digit 클래스
]	문자 리스트의 끝 부분
{2}	문자 리스트를 정확하게 2 회 반복 적용
-	또 다른 하이픈 기호
[	문자 리스트의 시작
[:digit:]	POSIX numeric digit 클래스
]	문자 리스트의 끝 부분
{4}	문자 리스트를 정확하게 4 회 반복 적용
|	대체(alternation) 메타문자 - 첫 번째 선택을 종료하고 두 번째 대체 표현식을 시작
[	문자 리스트의 시작
[:digit:]	POSIX numeric digit 클래스.
]	문자 리스트의 끝 부분
{9}	문자 리스트를 정확하게 9 회 반복
)	대체를 위해 사용된 서브표현식 그룹을 종료하는 괄호 기호
$	라인의 끝부분을 의미하는 앵커 메타문자 (매치된 패턴 이후에 어떤 문자도 뒤따라 올 수 없음)