ORACLE 10g 정규식 표현하기

본 자료는 일본 @IT(http://www.atmarkit.co.jp/fdb/index/index-db.html#sqlclinic)에 株式会社インサイトテクノロジー의 大道隆久씨가 연재한 SQLクリニック를 번역 재구성한 것입니다.

 

본연재는 SQL의 응용력을 가추고 싶은 엔지니어를 위해 다양한 테크닉을 소개한다. SQL의 기본 구문은 평이한 것이지만, 실무로 활용하려면  교과서적인 기술을 이해하는 것만으로는 불충분하다. 본연재는 저명한 메일 매거진 「오라!오라! Oracle - 듬뿍 검증 생활」을 발행하는 인사이트 테크놀로지의 컨설턴트를 집필진으로 맞이해 SQL의 센스 향상에 도움이 되는 각족 기술을 소개한다. 

 

## 테스트로 사용하는 샘플 테이블은 위의 링크로 부터 다운가능합니다.

 

 

■ Oracle 10g로 정규표현에 의한 문자열 조작 가능!!!

---

Oracle Database 10g부터 신규로 추가된 정규 표현 기능은 텍스트 형식 데이터를 취급하기 위한 강력한 툴입니다. 이것으로 SQL를 사용한 문자 데이터를 검색, 조작하는 능력이 큰폭으로 향상됩니다.

 

지금까지는 WHERE구 안에서 “LIKE”와 함께 사용하는 「%」나 「_」등에서 밖에 표현할 수 없었다 이제 애매한 문자열을 정규 표현을 사용해 문자수나 그 종류까지 지정할 수 있게 되었으므로 편리해 졌네요... 

Oracle 10g부터 서포트되는 정규 표현(REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, REGEXP_REPLACE 함수)을 소개합니다.

 

■ Oracle10g로 추가된 새로운 함수

---

Oracle9i까지 존재했던 함수에 「REGEXP_」를 붙여 정규 표현용의 함수가 준비되었습니다.(표1 참조) 차례로 조작 방법을 확인해 보게습니다. 

함수명칭	구문	설명
REGEXP_LIKE	REGEXP_LIKE(     열명,     조건 )	조건(정규 표현)을 사용해 애매한 조건 검색을 실시합니다 .검색 대상열에서는 문자 데이터형을 서포트합니다(CHAR, VARCHAR2, CLOB, NCHAR, NVARCHAR2 및 NCLOB는 서포트합니다만, LONG는 서포트하지 않습니다)
REGEXP_INSTR	REGEXP_INSTR(     문자열 또는 열명,     조건     (,개시 위치) )	지정한 조건(정규 표현)에 일치하는 부분의 최초의 값을 돌려줍니다.임의에, 검색을 개시하는 개시 위치를 지정하는 것도 가능합니다
REGEXP_SUBSTR	REGEXP_SUBSTR(     문자열 또는 열명,     조건 )	지정한 정규 표현에 일치하는 부분 문자열을 뽑아내 결과적으로 돌려줍니다
REGEXP_REPLACE	REGEXP_REPLACE(     열명 또는 문자열,     조건,     치환 문자열 )	지정한 정규 표현에 일치하는 부분을 지정한 다른 문자열로 치환합니다. 복잡한 치환/검색 조작을 가능하게 합니다
표1　정규 표현을 사용하는 함수

 

 

1．REGEXP_LIKE함수

^^^^^^^^^^^^^^^^^^^^^^^^^

정규 표현을 사용해 애매한 조건 검색을 실시할 경우에 이용합니다.

 

構文	REGEXP_LIKE(열명,조건)

 

 

 

SQL> SELECT * FROM TEST_REGEXP; COL1 ---------- ABCDE01234 01234ABCDE abcde01234 01234abcde SQL> SELECT * FROM TEST_REGEXP WHERE REGEXP_LIKE(COL1,'[0-9][A-Z]'); COL1 ---------- 01234ABCDE SQL> SELECT * FROM TEST_REGEXP WHERE REGEXP_LIKE(COL1,'[0-9][a-z]'); COL1 ---------- 01234abcde

리스트1　REGEXP_LIKE 함수를 사용한 fuzzy reference

 

조금 익숙해 지면 이하와 같은 체크 사용도 가능합니다.

 

SQL> ALTER TABLE QA_MASTER ADD CONSTRAINT QA_NO_CHK CHECK   2  (REGEXP_LIKE(QA_NO,   3  '^([[:alpha:]]{2}-[[:digit:]]{2}-[[:digit:]]{4})$')); Table altered. SQL> INSERT INTO QA_MASTER VALUES('QA-01-0001'); 1 row created. SQL> INSERT INTO QA_MASTER VALUES('00-01-0001'); INSERT INTO QA_MASTER VALUES('00-01-0001') * ERROR at line 1: ORA-02290: check constraint (ORAORA.QA_NO_CHK) violated

리스트2 Q/A관리 마스터에 QA번호를 등록할 때의 서식의 체크

 

이와 같이 허용된 데이터형식만을 체크할수 있습니다.

 

2．REGEXP_INSTR함수

^^^^^^^^^^^^^^^^^^^^^^^^^^

지정한 정규 표현에 일치하는 부분이 몇번째 문자인지를 돌려줍니다.

 

構文	REGEXP_INSTR(문자열 또는 열명, 조건)

 

 

 

SQL> SELECT COL1,REGEXP_INSTR(COL1,'[0-9]') ,   2  REGEXP_INSTR(COL1,'%') from TEST_REGEXP; COL1       REGEXP_INSTR(COL1,'[0-9]') REGEXP_INSTR(COL1,'%') ---------- -------------------------- ---------------------- ABCDE01234                          6                      0 01234ABCDE                          1                      0 @|=)(9&%$#                          6                      8 あいうえ3                           5                      0 6 rows selected.

리스트3　REGEXP_INSTR를 사용한 지정 문자의 위치 검색

 

N번째 문자 이후의 숫자 출력은......

 

SQL> SELECT COL1,REGEXP_INSTR(COL1,'[0-9]',4) FROM TEST_REGEXP; COL1       REGEXP_INSTR(COL1,'[0-9]',4) ---------- ---------------------------- ABCDE01234                            6 01234ABCDE                            4 abcde01234                            6 01234abcde                            4

리스트4　N번째 문자 이후의 숫자 출력

 

 

3．REGEXP_SUBSTR함수

^^^^^^^^^^^^^^^^^^^^^^^^^^^　

지정한 정규 표현에 일치하는 부분을 뽑아내 결과적으로 돌려줍니다.

 

構文	REGEXP_SUBSTR(문자열 또는 열명, 조건)

 

 

SQL> SELECT COL1,REGEXP_SUBSTR(COL1,'[C-Z]+') FROM TEST_REGEXP; COL1       REGEXP_SUBSTR(COL1,'[C-Z]') ---------- --------------------------- ABCDE01234 CDE 01234ABCDE CDE abcde01234 01234abcde

리스트 5　지정한 문자열을 선택

SQL> SELECT COL1,REGEXP_SUBSTR(COL1,'[C-Z]') FROM TEST_REGEXP; COL1       REGEXP_SUBSTR(COL1,'[C-Z]') ---------- --------------------------- ABCDE01234 C 01234ABCDE C abcde01234 01234abcde

리스트 6　해당하는 1 문자만을 선택

 

 

4．REGEXP_REPLACE함수

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

지정한 정규 표현에 일치하는 부분을 지정한 다른 문자열로 교체합니다.

 

構文	REGEXP_REPLACE(열명 또는 문자열, 조건, 치환 문자열)

 

 

SQL> SELECT COL1,REGEXP_REPLACE(COL1,'[0-2]+','*') FROM TEST_REGEXP; COL1       REGEXP_REPLACE(COL1,'[0-2]+','*') ---------- -------------------------------------------------- ABCDE01234 ABCDE*34 01234ABCDE *34ABCDE abcde01234 abcde*34 01234abcde *34abcde

리스트 7　지정한 부분의 문자를 다른 문자로 치환

 

■ 정규 표현의 다양한 활용 방법

---

SQL문으로 정규 표현이 가능하게 되어서 지금까지는 약간의 세공(PL/SQL나 조건식의 나열)이 필요함 작업을 간단하게 실현될 수 있게 되었습니다. 예를 들면 전자 메일이나 우편번호(해외등에서는 영문자도 포함된다) 격납열에의 검색·입력 체크도 간단합니다.그 외  데이타베이스 이행시에 문제가 되는 외자의 검출, 불필요한 공백 문자의 검출, 중복어의 출현의 식별 및 문자열의 해석·치환 등에도 위력을 발휘합니다.

 

이상과 같이, Oracle10g의 SQL 조작에 관한 추가된 기능입니다만, 알고 있으면 편리하겠져.. 

(계속 연제됩니다.)

[출처] 【제1회】Oracle 10g에서 정규 표현으로 문자열 조작하기!!|작성자 hirokorea