Oracle Regular Expressions 완전정복 오동규수석컨설턴트 1
오라클정규식이란? 강력한 Text 분석도구로서 Like 의한계를극복함. 유닉스의정규식과같음. Pattern-Matching-Rule 다양한메타문자제공. 2
Regular Expressions 정규식기본 Syntax. 함수사용법. 정규식고급 Syntax. 11g New Features 3
When ETL/ 전환 / 이행 Data Mining Data Cleansing 데이터검증 What 정규식의용도 중복단어의확인 특별한상태에서공백의제거 문자의파싱 (parsing) Text 에서전화번호, 우편번호, 이메일주소, 주민등록번호, IP 주소, 파일이름, 경로이름등을검증및추출 HTML 태그, 숫자, 날짜, 기타특정텍스트데이터와일치하는패턴을확인하고다른패턴으로대체하는것이가능 Constraints 로사용가능 4
Matching a Single Character.(dot) 는모든문자와 match 된다. 단엔터 (new line) 은예외임. SELECT emp_id, REGEXP_SUBSTR(text,'...-...') text FROM employee_comment WHERE REGEXP_LIKE(text,'...-...'); EMP_ID TEXT ---------- --------------- 7369 313-5351 7499 989-387- 7521 387-1698 989-387 387- 는의도되지않은결과이다. 5
Matching Any of a Set of Characters [] maching SET을이용하여숫자만나오게하자. SELECT emp_id, REGEXP_SUBSTR(text,'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]') text FROM employee_comment WHERE REGEXP_LIKE(text,'[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'); EMP_ID TEXT ---------- ------------- 7369 313-5351 7499 387-4321 7521 387-1698 같은표현 : [0123456789][0123456789][0123456789]-[0123456789][0123456789][0123456789][0123456789] [0123456789][0123456789][0123456789][0123456789] [0 [0-9] 를사용함으로써코딩량이줄어들었다. 하지만여전히 [0-9] 가반복되고있다. 6
Matching Any of a Set of Characters- 계속 Maching set 해석시주의사항 다음표현을해석하라 [01-3456-9] 0 이거나 1 에서 3 이거나 4 이거나 5 이거나 6 에서 9 인것 [.] 표현은문자하나를의미함을기억하자. 7
Matching Repeating Sequences Quantifier(repeat operator) {} 를이용하여 이용하여반복횟수를지정할수있다. SELECT emp_id, REGEXP_SUBSTR(text,'[0-9]{3}-[0-9]{4}') text FROM employee_comment WHERE REGEXP_LIKE(text,'[0-9]{3}-[0-9]{4}'); EMP_ID TEXT ---------- ------------- 7369 313-5351 7499 387-4321 7521 387-1698 Quantifier *? + {m} {m,} {m, n} 설명 0 회또는그이상횟수로매치 0 회또는 1 회매치 1 회또는그이상횟수로매치정확히 m 회매치최소한 m 회매치최소 m 회, 최대 n 회매치 8
다양한가능성을만족해야한다. XXX-XXXX 혹은 XXX-XXX-XXX 패턴이가능하다. 그리고구분자도 뿐만아니라, 나 SPACE 가올수있다. SELECT emp_id, REGEXP_SUBSTR(text, '[0-9]{3}[ 9]{3}[-. ][0-9]{3}[ 9]{3}[-. ][0-9]{4} 9]{4} [0 [0-9]{3}[ 9]{3}[-. ][0-9]{4}') text FROM employee_comment WHERE REGEXP_LIKE(text, '[0-9]{3}[ 9]{3}[-. ][0-9]{3}[ 9]{3}[-. ][0-9]{4} 9]{4} [0 [0-9]{3}[ 9]{3}[-. ][0-9]{4}') ; EMP_ID TEXT ---------- ------------- 7369 989 313-5351 7499 989-387-4321 7521 387-1698 7566 989.387.4444 7654 231-898-9823 7698 388-1234 7844 989-387.5359 7876 453-9999 는 OR 를의미함 9
Subexpressions 지역번호는괄호 () 가올수있고괄호는생략도가능하다. 또한지역번호자체도생략될수있다. SELECT emp_id, REGEXP_SUBSTR(text, '([0-9]{3}[-. ] \([0-9]{3}\) )?[0-9]{3}[-. ][0-9]{4}') text FROM employee_comment; EMP_ID TEXT ---------- --------------- 7369 989 313-5351 7499 989-387-4321 7521 387-1698 7566 989.387.4444 7654 231-898-9823 7698 (989) 388-1234 7782 7788 7839 7844 989-387.5359 7876 (231) 453-9999 7900 () 는 Grouping expression 을의미함.? 는생략되거나 1 번만올수있음. \ 는 ESCAPE 문자임. 10
Matching a Single Word 마지막 space 앞까지만출력하라 SELECT REGEXP_SUBSTR('Brighten the corner where you are', '.* ') text FROM dual; text ------------------------------ Brighten the corner where you 마지막 space 부터찾음을기억하라. 그렇다면첫번째 space 앞까지만출력하려면? 11
Check List Chapter 17 - Regular Expressions 첫번째단어를출력하라. Matching a Single Word SELECT REGEXP_SUBSTR('Brighten the corner where you are', '[^ ]*') text FROM dual; text ------------------------------ Brighten 12
Pattern Matching Modifiers Mod. i 설명대소문자를구분하지않는다. AbCd 가 return 예제 c n m x 대소문자를구분한다. (.) 는 default 로 new line 이 match 되지않으나 n 옵션을주면가능하다. multi-line mode 로검색이가능하다. 표현식의 whitespace 를무시하고 match 한다. REGEXP_SUBSTR('AbCd', 'abcd', 1, 1, 'i') 출력되지않음 : REGEXP_SUBSTR('AbCd', 'abcd', 1, 1, 'c') n 옵션을빼면매칭되지않음 : REGEXP_SUBSTR('a' CHR(10) 'd', 'a.d', 1, 1, 'n') ac 가 return, m 옵션을빼면매칭되지않음. REGEXP_SUBSTR('ab' CHR(10) 'ac', '^a.', 1, 2, 'm') abcd 가 return, 생략할경우출력되지않는다. REGEXP_SUBSTR('abcd', 'a b c d', 1, 1, 'x') 13
함수사용법 함수설명 REGEXP_LIKE: 정규표현에일치하는조건을검색 REGEXP_SUBSTR: 정규표현에일치하는부분문자열을 RETURN REGEXP_REPLACE: 정규표현에일치하는부분을지정한다른문자열로치환 REGEXP_INSTR: 정규표현에일치하는부분의위치값을 RETURN 사용법 (source, 표현식, Matching Modifiers ) (source, 표현식, 시작위치, 발생횟수, Matching Modifiers ) (source, 표현식, replace string, 시작위치, 발생횟수, Matching Modifiers) (source, 표현식, 시작위치, 발생횟수, return option, Matching Modifiers) 사용법의파란색인자는필수입력항목임. Optional 항목을추가할경우왼쪽부터추가할수있음. REGEXP_INSTR 는특이하게 RETURN OPTION 이있다. 0 은시작위치를 RETURN 1 은종료위치를 RETURN 한다. DEFAULT 는 0 임. 14
Using Backreferences Backreference 와 REGEXP_REPLACE를이용하여 Ellen Hildi Smith 라는이름을 SPACE 1 칸으로구분해서각각 Smith, Ellen Hildi로변환하라. SELECT REGEXP_REPLACE( 'Ellen Hildi Smith', '(.*) (.*) (.*)', '\3, \1 \2') TEXT FROM dual TEST ------------------ Smith, Ellen Hildi 15
Using Named Character Classes Character Class [:alpha:] [:lower:] [:upper:] [:digit:] [:alnum:] [:space:] [:punct:] [:cntrl:] [:print:] 설명 알파벳문자소문자알파벳문자대문자알파벳문자숫자알파벳 / 숫자출력되지않는공백문자 (carriage return, newline, vertical tab, form feed) 등구두점기호 ( 출력되지않는 ) 컨트롤문자출력가능한문자 16
REGEXP_LIKE Data 검증예제 : 우편번호컬럼에서숫자가아닌문자가포함되어있는 row 를찾아라. SELECT zip FROM zipcode WHERE REGEXP_LIKE(zip, '[^[:digit:]]') ZIP ----- ab123 123xy 007ab abcxy 17
REGEXP_INSTR Data 에서특정위치찾기 : SOURCE 에서숫자 XXXXX 로끝나거나 XXXXX-XXXX 로끝나는지점의위치를찾아라. SELECT REGEXP_INSTR( 'Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234', ' [[:digit:]]{5}(-[[:digit:]]{4})?$ [[:digit:]]{4})?$ ) AS starts_at FROM dual STARTS_AT ---------- 44 Quantifier *? + {m} {m,} {m, n} 설명 0 회또는그이상횟수로매치 0 회또는 1 회매치 1 회또는그이상횟수로매치정확히 m 회매치최소한 m 회매치최소 m 회, 최대 n 회매치 18
REGEXP_SUBSTR Data 에서특정부분발췌하는예제 : comma를검색하고그뒤에 space 한번이나오고계속하여 comma가아닌문자가 1 회또는그이상반복되는패턴을검색한후마지막으로또다른쉼표를검색해야한다. SELECT REGEXP_SUBSTR( 'first field, second field, third field', ', [^,]+, ) as test FROM dual TEST ------------------, second field, 19
REGEXP_REPLACE Data 에서특정부분을제거하여발췌하는예제 : 아래 source 에서 Space 2칸이상을 space 1 칸으로만들어라. SELECT REGEXP_REPLACE( 'Joe Smith', '( ){2,}' ' ') AS RX_REPLACE FROM dual RX_REPLACE ---------- Joe Smith 이것을 REPLACE 함수를사용할경우아래처럼나타낼수있다. SELECT REPLACE('Joe Smith', ', ' ') AS REPLACE FROM dual 하지만 SPACE 2칸을지정하였기때문에 3칸이상은수행되지않는다. 20
REGEXP_COUNT Data 에서특정부분이반복되는 COUNT 를 RETURN 한다. SELECT REGEXP_COUNT( http://scidb.tistory.com,.', 1, 'i') AS "Count TEST" FROM dual ; 11g New feature Count TEST ---------- 2 문법 (source, 표현식, 시작위치, Matching Modifiers ) 10g 에서구현하기. LENGTH(string) - LENGTH(REPLACE(string, i')) 21
Subexpression to Return 파라미터 () 를이용하여 return 되는집합을선택할수있다. Subexpression 중에서 2 번째집합을 return 하라. SELECT REGEXP_SUBSTR( 'www.oracle-developer.net', '(\.)([a-z-]+)(\.)', --> subexpressions 을사용한표현식 1, --> 시작위치 1, --> 발생횟수 'i', --> Matching Modifiers 2 --> 11g: subexpression to return ) AS url_middle_11g FROM dual; URL_MIDDLE_11G ---------------- oracle-developer 11g New feature REGEXP_SUBSTR 와 REGEXP_INSTR 만가능함 10g 에서구현하기 : REPLACE( REGEXP_SUBSTR('www.oracle-developer.net', \.[a.[a-z-]+ ]+\.'), '.') 22
정리 1.Dot 사용법 2.[] maching set 사용법 3.{} 를이용하여반복횟수제어하기 4. 를이용하여다양한조건만족하기 5.Subexpression () 를이용하여 group 표현식으로나타내기 6. Anchoring( 시작및종료 ) 문자이용하기 7.Matching a Single Word 23
Check List Chapter 17 - Regular Expressions 정리 Pattern Matching Modifiers REGEXP_INSTR REGEXP_LIKE REGEXP_REPLACE REGEXP_SUBSTR REGEXP_COUNT 24
Check List Chapter 17 - Regular Expressions 정리 1.Using Backreferences 2.Using Named Character Classes 3.11g New Features REGEXP_COUNT Subexpression to Return 파라미터 4.REGEXP_LIKE와 REGEXP_COUNT를제외하면 Function Based Index 를만들수있고 Constraint 로활용가능하다. 25
별첨 정규식 Metacharacters 정리.doc 감사합니다. 26