페이지 1 / 5 정규표현식을이용한 SQL 구문의개선 저자 - Alice Rischert Oracle Database 10g 의정규표현식기능을텍스트데이터처리를위한강력한도구로활용할수있습니다. Oracle Database 10g 에추가된새로운기능을이용하여문자데이터의검색, 처리능력을극적으로개선할수있습니다. 정규표현식 (regular expression) 이라불리는이기능은, 텍스트패턴을기술하기위한일종의표기법으로, 이미오래전부터다양한프로그래밍언어와 UNIX 유틸리티를통해지원되어왔습니다. 오라클의정규표현식은 SQL 함수와 WHERE 절연산자의형태로제공됩니다. 정규표현식에익숙하지않은독자라면, 이문서를통해전혀새롭고강력한기능을체험하실수있을것입니다. 또정규표현식에이미친숙한독자분들은, Oracle SQL 언어의문맥에이기능을적용하는방법을이해하는기회로활용하실수있습니다. 정규표현식이란? 정규표현식은하나또는그이상의문자열과메타문자 (metacharacter) 로구성됩니다. 가장단순한형태의정규표현식은 cat 과같은단하나의문자열로만구성될수있습니다. 이정규표현식은문자 c 와문자 a, 문자 t 의순서를갖는패턴매치문자열로 cat, location, catalog 등의문자열과매치됩니다. 메타문자는정규표현식을구성하는문자들을처리하는방법을명시하기위한알고리즘을제공합니다. 다양한메타문자의의미를이해한다면, 정규표현식이텍스트데이터를비교하고대체하는용도로매우유용하게활용될수있음을금방깨닫게되실것입니다. 데이터의검증, 중복단어의확인, 불필요한공백의제거, 문자의파싱 (parsing) 등정규표현식의활용방법은실로다양합니다. 정규표현식을이용하여전화번호, 우편번호, 이메일주소, 주민등록번호, IP 주소, 파일이름, 경로이름등을검증할수도있습니다. 또 HTML 태그, 숫자, 날짜, 기타특정텍스트데이터와일치하는패턴을확인하고다른패턴으로대체하는것이가능합니다. Oracle Database 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) 가사용되어야합니다. 캐럿 (^) 메타문

페이지 2 / 5 자는라인의시작을, 달러 ($) 기호는라인의끝을의미합니다 ( 표 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 는이러한메타문자들을요약하고있습니다. 지금까지설명한것말고도다양한메타문자가있지만, 여기에서는본문서에서예제로사용되는정규표현식을이해할수있는정도만이해하고넘어가기로합니다.

페이지 3 / 5 REGEXP_LIKE 연산자 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 는문자리스트를구성하는용도로만사용되므로이와같은처리가필요합니다. REGEXP_INSTR 함수 이함수는패턴의시작위치를반환하며, 따라서 INSTR 함수와유사한형태로동작합니다. REGEXP_INSTR 함수의사옹방법은표 6 에서확인할수있습니다. 두함수의가장중요한차이는 REGEXP_INSTR 를이용하는경우특정문자열이아닌패턴을지정할수있으며, 따라서훨씬유연한검색이가능하다는사실입니다. 다음예에서는 REGEXP_INSTR 을사용하여 Joe Smith, 10045 Berry Lane, San Joseph, CA 91234 문자열에서 5 개의숫자로구성된우편번호패턴의시작부분을반환하고있습니다. 정규표현식 [[:digit:]]{5} 를사용하는경우우편번호가아닌집주소번호의시작위치를얻게됩니다 ( 처음으로검색되는 5 개연속숫자패턴이 10045 이기때문입니다 ). 따라서 $ 메타문자를사용하여표현식의앵커를라인끝부분으로지정해야합니다. 이렇게하면집주소번호에관계없이우편번호의시작위치를얻을수있습니다. SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234', '[[:digit:]]{5}$') AS rx_instr RX_INSTR 45 좀더복잡한패턴의작성 앞의예의우편번호패턴을확장하여네가지숫자를포함하는패턴을만들어보기로합시다. 새로작성된패턴이아래와같습니다 : [[:digit:]]{5}(-[[:digit:]]{4})?$. 소스문자열이 5 개숫자로종료되든, 또는 "5 개숫자 + 4 자리우편번호 " 포맷을갖든, 패턴의시작위치를얻을수있습니다.

페이지 4 / 5 SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234', ' [[:digit:]]{5}(-[[:digit:]]{4})?$') AS starts_at STARTS_AT 44 위의예에서괄호로묶인서브표현식 (-[[:digit:]]{4}) 는반복연산자? 로지정된조건에따라 0 회또는 1 회반복됩니다. 다시말하지만, 기존의 SQL 함수를이용하여같은결과를얻어내려면아무리 SQL 전문가라해도쉽지않은작업이될것입니다. 표 7 은정규표현식을구성하는각문자와메타문자의의미를설명하고있습니다. REGEXP_SUBSTR 함수 REGEXP_SUBSTR 함수는 SUBSTR 함수와마찬가지로문자열의일부를추출합니다. 표 8 은새로운함수의사용법을설명하고있습니다. 아래예제에서는 [^,]*, 패턴에매치되는문자열이반환됩니다. 정규표현식은공백에이어사용된쉼표를검색하고, 쉼표가아닌문자가 0 회또는그이상반복되는패턴을검색 ([^,]*) 한후마지막으로또다른쉼표를검색합니다. 이패턴은쉼표로구분된문자열 (comma-separated values) 과유사한배열을갖습니다. SELECT REGEXP_SUBSTR('first field, second field, third field', ', [^,]*,') REGEXP_SUBSTR('FIR --------, second field, REGEXP_REPLACE 함수 특정문자열을다른문자열로대체하는기존의 REPLACE SQL 함수의기능을잠시되짚어보겠습니다. 데이터의텍스트에필요이상의공백기호가존재하는상황에서, 이를단일공백기호로대체하는경우를가정해봅시다 REPLACE 함수를사용할때에는대체할공백기호의숫자를정확하게지정해야합니다. 하지만, 필요없는공백의수가일정하리라는보장은없습니다. 아래예는 Joe 와 Smith 사이에 3 개의공백기호가존재하는경우를검색하고있습니다. REPLACE 함수의매개변수는두개의공백기호를하나의공백기호로대체할것을명시하고있습니다. 하지만 Joe 와 Smith 사이에 3 개의공백기호가존재하는경우에는여전히필요없는공백이하나남게됩니다. SELECT REPLACE('Joe AS replace REPLACE --------- Joe Smith Smith',' ', ' ') REGEXP_REPLACE 한층개선된문자열대체기능을제공합니다. 그사용법은표 9 에서설명되고있습니다. 아래쿼리는두개또는그이상의공백기호를하나의공백기호로대체합니다. ( ) 서브표현식은하나의공백기호를포함하며 {2,} 의조건에의해지정된대로 2 회또는그이상반복되는조건을명시합니다. SELECT REGEXP_REPLACE('Joe '( ){2,}', ' ') AS RX_REPLACE RX_REPLACE Smith',

페이지 5 / 5 Joe Smith