SQL Regular expression

Regular_exp_sample.sql

※ 정규표현식(Regular expression)

: 주어진 문장 패턴과 매치시킬 수 있는 어떤 표현을 기술할 수 있는 기호와 원소들의 집합으로 매우 강력하고 실용적인 패턴 매칭기법이다.

- POSIX 표준 문법을 따른다.

- 다양한 매치와 검색을 정의하는 메타기호들의 조합으로 이루어진다.

- 대 ․ 소문자를 구분한다.

[자주 쓰는 메타기호]

* : 0 번 이상 나타남

ex) Ad*은 Ada, Adb, Adcbdd 와 매치되지만 Acd, add 와는 매치되지 않는다.

? : 글자 하나와 매치

ex) A?c 는 Aac, AAc, Adc 와 매치되지만 Bcc, zzc 와는 매치되지 않는다.

^ : 라인의 시작부분

ex) ^abc 는 abch 와 매치되지만 habc 와는 매치되지 않는다.

$ : 라인의 끝부분

ex) Abc$는 Abc 와 매치되지만 Abcadd 와는 매치되지 않는다. [] : 괄호 안에 있는 문자 중에 하나와 매치

ex) A[a-c]b 는 Aab, Acb 와 매치되지만 Abcadd 와는 매치되지 않는다.

{m} : m 번 나타남

ex) Myname[0-9]{3}은 Myname9, Myname99, Myname222 와 매치되지만, Myname2222 와는 매치되지 않는다.

{m,n} : m 번 이상, n 번 이하 나타남.

ex) Myname[0-9]{3,5}는 Myname9, Myname99, Myname2222 와 매치되지만, Myname222222(2 가 6 개)와는 매치되지 않는다.

∖n : 백레퍼런스라고 한다. 이전 표현식을 n 번 반복.

ex) [aeiou]∖2 은 aaron 과 매치되지만, aron 과 매치되지 않는다.

※ 10g 에서 정규표현식 사용하기

: 10g 에서는 몇 가지 새로운 함수를 통해 정규표현식을 사용한다.

1. regexp_like 함수

: SQL 의 like 연산자와 비슷한 것으로 정규표현식의 패턴 매칭 기능을 통해 like 를 더 확장한 것이다.

입력으로는 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB type 이 올수 있다.

- 대 소문자 구별을 하지 않도록 하는 옵션 있음

- 여러 줄에 걸쳐있는 문자열 검색 가능

- 정규표현식이 NULL 인 경우 정의되지 않은 값(unknown value)을 반환한다.

형식 : regexp_like(search_string, pattern [, match_option]);

‧ search_string : 검색할 문장

‧ pattern : 정규표현식(512byte 이하)

‧ match_option : 매칭 방법을 결정하는 옵션(문자열) C : 대소문자 구별(default),

| : 대소문자 무시, N :

.(period)이 개행문자(newline)와도 매치됨,

M : 검색하는 문자열을 멀티 라인으로 간주. 10g 에서 ^,$을 전체 문자열의 시작과 끝에 매치시키지 않고 한 라인의 시작과 끝에 배치되도록 한다.

[ex.]

SQL> DESC test_expressions

NameNull?Type

----------------------------------------------- CHAR_VALUEVARCHAR2(30)

DATE_VALUEDATE

SQL> SELECT * FROM test_expressions;
CHAR_VALUEDATE_VALUE

-----------------------------------------------

abcd27-DEC-03

abb27-DEC-03 zzz27-DEC-02 zbz30-JUN-03 zabz30-JUN-03 aaron30-JUN-03

> CHAR_VALUE column 에서 ab 로 시작하는 것만 검색

SQL> SELECT * FROM test_expressions WHERE REGEXP_LIKE(char_value, '^ab*');
CHAR_VALUEDATE_VALUE

-----------------------------------------------
abcd27-DEC-03

abb27-DEC-03

> 두 번째 글자가 b 인 것만 검색

SQL> SELECT * FROM test_expressions WHERE REGEXP_LIKE(char_value, '^.b.');
CHAR_VALUEDATE_VALUE

-----------------------------------------------
abcd27-DEC-03

abb27-DEC-03 zbz30-JUN-03

> 날짜비교 : to_char 함수를 이용해 날짜를 문자열로 변환(8 분에 생성된 데이터)
SQL> SELECT TO_CHAR(date_value, 'mm/dd/yyyy hh24:mi:ss')

FROM test_expressions

WHERE REGEXP_LIKE(TO_CHAR(date_value, 'yyyy/mi'), '200[2-3]/08');
TO_CHAR(DATE_VALUE)

---------------------

12/27/2003 12:08:36

12/27/2003 12:08:43

12/27/2002 12:08:51

06/30/2003 12:08:59

> 특정 문자들이 여러 번 반복해서 나타나는 경우(z 나 a 가 반복되는 경우)
SQL> SELECT char_value FROM test_expressions

WHERE REGEXP_LIKE(char_value, '(([za]))/2', 'i');
CHAR_VALUE

------------
aaron

zzz

2. regexp_instr 함수

: instr 연산을 확장한 것으로 정규표현식을 이용해 입력된 문장을 검색한다. return_option 설정에 따라 일치하는
부분의 시작 위치, 끝 위치를 숫자로 반환해 준다. 입력으로는 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB type 이 올 수 있다.
정규표현식이 NULL 인 경우 이 함수는 정의되지 않은 값을 반환한다.

형식 : redexp_instr(search_string, pattern [, position [, occurrence

[, return_option [, match_option]]]]);

‧ search_string : 검색할 문장

‧ pattern : 정규표현식(512byte 이하)

‧ position : 어디서부터 검색할 것인지를 나타내는 양의 정수로, 기본값은 1 이다.

‧ occurrence : 검색할 패턴의 출연 횟수.

기본값은 1 이며 이 경우 처음 매치되는 부분을 찾는다.

‧ return_option : 0 인 경우 매치되는 부분의 시작 부분을 반환하며, 0 이 아닌 경우에는 끝 부분(검색된 문자열 다음 번 위치)이 반환된다.

‧ match_option : 매칭 방법을 결정하는 옵션(문자열).

[ex.]

> b 가 두 번 등장하는 데이터를 정규표현식을 사용하지 않고 검색

SQL> SELECT * FROM test_expressions

WHERE REGEXP_INSTR(char_value, 'b', 1, 2, 0) > 0;

CHAR_VALUEDATE_VALUE

-------------------------------------
abb27-DEC-03

> b 또는 c 가 두 번 이상 문자열에 들어 있는 데이터 검색

SQL> SELECT * FROM test_expressions

WHERE REGEXP_INSTR(char)value, '[b-c]', 1, 2, 0) > 0;
CHAR_VALUEDATE_VALUE

-----------------------------------------------
abcd27-DEC-03

abb27-DEC-03

> 위치 반환

: 반환되는 위치는 검색된 문자열 끝이 아닌 그 다음 글자 위치를 결과로 반환

SQL> SELECT REGEXP_INSTR('This is a test', '[^ ]+', 1, 2) FROM dual;

REGEXP_INSTR('This is a test', '[^ ]+', 1, 2)

------------------------------------

3. regexp_replace 함수

: replace 함수의 확장판으로 정규표현식을 이용해 입력된 문장을 검색하고 매치되는 부분을 다른 문자열로 바꿔서 반환해 준다.
입력값으로는 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB type 이 올 수 있다.

형식 : regexp_replace(search_string, pattern [, replacestr [, position [, occurrence

[, match_option]]]]);

‧ search_string : 검색할 문장

‧ pattern : 정규표현식(512byte 이하)

‧ replacestr : 검색된 부분과 대체될 문자열

[ex]

> This is the wild west west -> This is the wild west dude

SQL> SELECT REGEXP_REPLACE ('this is the wild west', 'west$', 'dude') FROM dual;

REGEXP_REPLACE ('THIS IS THE WILD WEST')

---------------------------------------
this is the wild west dude

> 전화번호의 대쉬문자(-)를 마침표(.)로

SQL> SELECT REGEXP_REPLACE ('206-555-1212',

'([[:digit:]]{3})∖-([[:digit:]]{4}', '∖1.∖2.∖3') FROM dual;

REGEXP_REPALCE

---------------

206.555.1212

----> [:digit:] : 숫자를 표기하기 위한 문자 클래스 정의

[[:digit:]]{3}은 [0-9]{3}과 동일한 표현

4. regexp_substr 함수

: substr 함수의 확장판으로 정규표현식을 이용해 검색한 문자열의 부분문자열(substring)을 반환한다. regexp_instr 함수는 문자열의
시작위치, 끝 위치를 반환하지만 regexp_substr 함수는 실제 문자열을 결과값으로 반환한다. 입력으로는 CHAR, VARCHAR2, NCHAR,
NVARCHAR2, CLOB, NCLOB type 이 올 수 있다.

형식 : regexp_substr(search_string, pattern [, position [, occurrence [, match_option]]]]);

‧ search_string : 검색할 문장

‧ pattern : 정규표현식(512byte 이하)

‧ position : 어디서부터 검색할 것인지를 나타내는 양의 정수로, 기본값은 1 이다.

‧ occurrence : 검색할 패턴의 출연 횟수.

[ex]

> b 가 두 번 나온 레코드 찾기

SQL> SELECT REGEXP_SUBSTR(char_value, 'b', 1, 2) FROM test_expressions

WHERE REGEXP_INSTR(char_vaule, 'b', 1, 2) >0;
REGEXP_SUBSTR(CAHR_VALUE)

--------------------------
b

> b 나 c 문자가 들어있는 데이터를 검색한 후 b, c, d 문자로만 이루어진 부분 출력 SQL> SELECT char_value,
REGEXP_SUBSTR(char_value, '([b-d]+∕?) {0, 5}∕?') FROM test_expressions

WHERE REGEXP_INSTR(char_value, '[b-c]' 1, 2) > 0;
CHAR_VALUEREGEXP_VALUE

----------------------------------------
abcdbcd

abbbb

정규표현식의 기본 문법

정규표현식은 소프트웨어에 따라서 방식이나 지원 범위가 다를 수 있습니다.

“^The”	"The"로 시작하는 문자열
"of despair$"	"of despair"로 끝나는 문자열
"^abc$"	"abc"로 시작하고 "abc"로 끝나는 문자열 ("abc" 라는 문자열도 해당됨)
"notice"	"notice"가 들어 있는 문자열

"ab*"	a 다음에 b가 0개 이상 ("a", "ab", "abbb", 등)
"ab+"	a 다음에 b가 1개 이상 ("ab", "abbb", 등)
"ab?"	a 다음에 b가 있거나 없거나 (ab 또는 a)

"ab{2}"	a 다음에 b가 2개 있는 문자열 ("abb")
"ab{2,}"	a 다음에 b가 2개 이상 ("abb", "abbbb", 등.)
"ab{3,5}"	a 다음에 b가 3개에서 5개 사이 ("abbb", "abbbb", 또는 "abbbbb")

'*', '+', '?'는 각각 "{0,}", "{1,}", "{0,1}"와 같습니다.

( )는 문자열을 묶음 처리할 때 사용
"a(bc)*"	a 다음에 “bc”가 0개 이상 (묶음 처리)
"a(bc){1,5}"	a 다음에 “bc”가 1개에서 5개 사이

"hi\|hello"	"hi"나 "hello"가 들어 있는 문자열
(응용) "(b\|cd)ef"	"bef" 또는 "cdef"
(응용) "(a\|b)*c"	a와 b가 섞여서 여러번 나타나고 그뒤에 c가 붙어있는 패턴

. (점) 임의의 한 문자

ex) "^.{3}$" 3문자로만 되어 있는 문자열

[] 괄호 안에 있는 내용 중 임의의 한 문자 [] 안에서 첫문자로 ‘^’를 쓰면 괄호 내용의 부정 – 즉 괄호 안에 포함되지 않는 한 문자
"[ab]"	a 또는 b ("a\|b"와 동일한 표현)
"[a-d]"	소문자 'a'에서 'd'까지 ("a\|b\|c\|d"또는 "[abcd]"와 동일)
(응용) "^[a-zA-Z]"	영문자로 시작하는 문자열
(응용) "[0-9]%"	% 문자 앞에 하나의 숫자가 붙어 있는 패턴
(응용) "%[^a-zA-Z]%"	두 % 문자 사이에 영문자가 없는 패턴

특수 문자 자체를 검색하기 및 사용하기
\^	^	\.	.
\[	[	\$	$
\(	(	\)	)
\\|	\|	\*	*
\+	+	\?	?
\{	{	\\	\
\n	줄넘김문자	\r	리턴문자
\w	알파벳과 _ (밑줄)	\W	알파벳과 _이 아닌 것
\s	빈 공간	\S	빈 공간이 아닌 것
\d	숫자	\D	숫자가 아닌 것
\b	단어와 단어 사이의 경계	\B	단어 사이의 경계가 아닌 곳
\t	Tab문자	\xnn	16진수 nn에 해당하는 문자

[ ] 안에서는 특수 문자가 모두 효력을 잃게 됩니다.

검색 + 치환을 위한 하부식(부분식)

( )로 둘러싼 부분은 각각 하나의 덩어리로 취급해서, 검색시 ( ) 안에 해당되는 내용들을 변

경할 내용에서 그대로 가져다 이용할 수 있습니다. 검색된 각각의 ( )안에 해당되는 내용은 변경할 내용에서 $1, $2, .. 등으로 지정해서 쓸 수 있습니다.

예제) mp3파일 이름 바꾸기

검색 : (.*) - (.*)\.mp3 .*은 길이에 상관없이 임의의 문자열, \.은 점

치환 : $2 - $1.mp3 앞에서 검색한 ( )안에 해당되는 내용끼리 순서 바꾸기

ex) "제목 - 연주자.mp3" ⇒ "연주자 - 제목.mp3"

앞에서 정의한 하부식을 다시 활용하기 (제가 잘못 이해한 것일 수도 있는데)

\n은 ( ) 하부식 중에서 n번째 하부식을 가리킵니다.

예제) (.+)\1+

\1로 되어 있으니까 첫번째 부분식 (.+)를 가리킵니다. 위 내용을 해석하자면, 일단 (.+)가 있으니까 이에 해당되는 내용을 찾고, \1+이 있으니까 첫번째 부분식 (.+)와 똑같은 내용이 그 뒤에 1번 이상 있는 문자열을 찾습니다.

예제) abab같은 문자열이 위에 해당되는데, 일단 (.+) 즉 임의의 문자열 ab를 찾고 그 뒤에

\1+로 첫번째 부분식을 다시 1번 이상 있는 것을 찾으니까 뒤의 ab가 이에 해당합니다.

변경자 – 검색 방식 변경
(?i)	대소문자 무시 (기본값)
(?-i)	대소문자 구분
(?g)	"greedy" 모드로 전환 (기본값)
(?-g)	"greedy" 모드 해제, 따라서 "+"는 "+?"과 동일한 것으로 인식

============================================================

Regular_Exp_LAB

================================================================================

Code Listing 1: Finding phone numbers in the park descriptions

SQL> SELECT park_name, description
FROM michigan_park
WHERE REGEXP_LIKE(description, '...-....');

Mackinac Island State Park
Michigan's first state park encompasses approximately 1800 acres
of Mackinac Island. The centerpiece is Fort Mackinac, built in 1780 by
the British to protect the Great Lakes Fur Trade. For information by
phone, dial 800-44-PARKS or 517-373-1214.

Fort Wilkens State Park
Located almost at the very tip of the Keewenaw Peninsula,
Fort Wilkens is a restored army fort built during the copper rush.
Camping is available. For the modern campground, phone (800) 447-2757. For
group-camping, phone 906.289.4215. For information on canoe, kayak, and
other boat rentals, call the concession office at (906) 289-4210.

Muskallonge Lake State Park
A 217-acre park located on the site of an old lumber town, Deer Park.
Shower and toilet facilities are available, as are campsites with
electricity.

Porcupine Mountains State Park
Michigan's largest state park consists of some 60,000 acres
of mostly virgin timber. Over 90 miles of trails are available
to backpackers and hikers. Downhill skiing is available in winter.
Rustic cabins are available. To reserve a cabin, call (906) 885-5275.

=================================================================================

Code Listing 2: Increasing the constraints on the phone number pattern
SQL> SELECT park_name, description
FROM michigan_park
WHERE REGEXP_LIKE(description, '[0-9]{3}[-.][0-9]{4,4}');

Tahquamenon Falls State Park
One of the largest waterfalls east of the Mississippi is found
within this park's 40,000+ acres. Upper Tahquamenon Falls is some 50 feet
high, 200 feet across, and supports a flow that has been known to reach
50,000 gallons/second. The park phone is 906.492.3415.

=================================================================================

Code Listing 3: Constraining the phone number column

SQL> ALTER TABLE michigan_park
     ADD (CONSTRAINT phone_number_format
          CHECK (REGEXP_LIKE(park_phone,
          '^$[[:digit:]]{3}$ [[:digit:]]{3}-[[:digit:]]{4}$')));

Table altered.

SQL> INSERT INTO michigan_park (park_name, park_phone)
VALUES ('Warren Dunes State Park','616.426.4013');

INSERT INTO michigan_park (park_name, park_phone)
*
ERROR at line 1:

ORA-02290: check constraint (TK32VP.PHONE_NUMBER_FORMAT) violated

SQL> INSERT INTO michigan_park (park_name, park_phone)
VALUES ('Warren Dunes State Park','(616) 426-4013');

=================================================================================

Code Listing 4: Regularizing the format of the phone numbers

SQL> UPDATE michigan_park
     SET description = REGEXP_REPLACE(description,
                       '([[:digit:]]{3})[-.]([[:digit:]]{3})[-.]([[:digit:]]{4})',
                       '(\1) \2-\3');

=================================================================================

Code Listing 5: Finding the acreage for each park

SQL> COLUMN park_name format a30
SQL> COLUMN acres format a13
SQL> SELECT park_name, SUBSTR(description,
            REGEXP_INSTR(description, '[^ ]+ acres|[^ ]+-acre',1,1,0,'i'),
            REGEXP_INSTR(description, '[^ ]+ acres|[^ ]+-acre',1,1,1,'i')
              - REGEXP_INSTR(description, '[^ ]+ acres|[^ ]+-acre',1,1,0,'i')) acres
     FROM michigan_park
     WHERE REGEXP_LIKE(description, '[^ ]+ acres|[^ ]+-acre','i');

PARK_NAME ACRES
____________________________ ___________

Mackinac Island State Park      1800 acres
Muskallonge Lake State Park     217-acre
Porcupine Mountains State Park 60,000 acres
Tahquamenon Falls State Park    40,000+ acres

=================================================================================

Code Listing 6: Using REGEXP_SUBSTR to extract acreage information

SQL> SELECT park_name,
            REGEXP_SUBSTR(description,'[^ ]+[- ]acres?',1,1,'i') acres
     FROM michigan_park
     WHERE REGEXP_LIKE(description, '[^ ]+[- ]acres?','i');

PARK_NAME                      ACRES
____________________________    ___________
Mackinac Island State Park      1800 acres
Muskallonge Lake State Park     217-acre
Porcupine Mountains State Park 60,000 acres
Tahquamenon Falls State Park    40,000+ acres

=================================================================================
test_expressions

create table test_expressions
(char_value varchar2(30), date_value date);

--Table created.

insert into test_expressions
values ('abcd', to_date('27-DEC-09','DD-MON-RR'));

--1 row created.

insert into test_expressions
values ('abb',to_date('27-DEC-09','DD-MON-RR'));

--1 row created.

insert into test_expressions
values ('zzz', to_date('27-DEC-08','DD-MON-RR'));

--1 row created.

insert into test_expressions
values ('zbz', to_date('15-DEC-08','DD-MON-RR'));

--1 row created.

insert into test_expressions
values ('zabz', to_date('15-JUN-09','DD-MON-RR'));

--1 row created.

insert into test_expressions
values ('aaron',to_date('15-JUN-09','DD-MON-RR'));

--1 row created.

commit;

--Commit complete.

저작자표시 비영리 변경금지

'oracle10R2' 카테고리의 다른 글

Benefit ASM (0)	2011.06.19
oracle 10g partition 적용 가이드 (0)	2011.06.18
Oracle Database 10g：SQL Fundamentals (0)	2011.06.17
Oreilly - Oracle PL SQL Programming 11gR2 (5th) (0)	2011.06.09
SQL Tuning 10g 정리 (0)	2011.05.24

OCM10gR2

SQL Regular expression

'oracle10R2' 카테고리의 다른 글

티스토리툴바

SQL Regular expression

'oracle10R2' 카테고리의 다른 글

'oracle10R2' Related Articles

티스토리툴바