Oracle 10g 政則表示式介紹

*****************************************

lynx286原創, 首發 歡迎轉貼, 但轉貼請不要刪除此段版權說明.

*****************************************

[@more@]

Agenda
What Is a Regular Expression?
Using Regular Expressions With Oracle Database 10g
Basic Examples of Regular Expressions
Using with DDL

What Is a Regular Expression?
A regular expression comprises one or more character literals and/or metacharacters.
In its simplest format, a regular expression can consist only of character literals, such as the regular expression cat. It is read as the letter c followed by the letters a and t and this pattern matches strings such as cat, location, and catalog.
Metacharacters provide algorithms that specify how Oracle should process the characters that make up a regular expression.
When you understand the meaning of the various metacharacters, you will see that regular expressions are powerful for isolating and replacing specific textual data.
Purpose
Data validation, identification of duplicate word occurrences, detection of extraneous white spaces, or parsing of strings are just some of the many uses of regular expressions.
You can apply them in order to validate the formats of phone numbers, zip codes, email addresses, Social Security numbers, IP addresses, filenames and pathnames, and so on.
Furthermore, you can locate patterns such as HTML tags, numbers, dates, or anything that fits any pattern within any textual data and replace them with other patterns.
Using Regular Expressions With Oracle 10g
To harness the power of regular expressions, you can exploit the newly introduced Oracle SQL REGEXP_LIKE operator and the REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_REPLACE functions. You will see how this new functionality supplements the existing LIKE operator and the INSTR, SUBSTR, and REPLACE functions.
In fact, they are similar to the existing operator and functions but now offer powerful pattern-matching capabilities. The searched data can be simple strings or large volumes of text stored in the database character columns. Regular expressions let you search, replace, and validate data in ways you have never thought of before, with a high degree of flexibility.
Syntax
Official document:

運算子
'^' 匹配輸入字串的開始位置，在方括號表示式中使用，此時它表示不接受該字符集合。 '$' 匹配輸入字串的結尾位置。如果設定了 RegExp 物件的 Multiline 屬性，則 $ 也匹配 'n' 或 'r'。 '.' 匹配除換行符 n之外的任何單字元。 '?' 匹配前面的子表示式零次或一次。 '*' 匹配前面的子表示式零次或多次。 '+' 匹配前面的子表示式一次或多次。 '( )' 標記一個子表示式的開始和結束位置。 '[]' 標記一箇中括號表示式。 '{m,n}' 一個精確地出現次數範圍，m=Syntax
字元簇：
[[:alpha:]] 任何字母。 [[:digit:]] 任何數字。 [[:alnum:]] 任何字母和數字。 [[:space:]] 空格。 [[:upper:]] 任何大寫字母。 [[:lower:]] 任何小寫字母。 [[:punct:]] 任何標點符號。 [[:xdigit:]] 任何16進位制的數字，相當於[0-9a-fA-F]。

各種運算子的運算優先順序:
轉義符
圓括號和方括號: (), (?:), (?=), []
限定符: *, +, ?, {n}, {n,}, {n,m}
位置和順序: ^, $, any metacharacter
“或”操作: |
Examples
create table test(mc varchar2(60));
insert into test values('112233445566778899'); insert into test values('22113344 5566778899'); insert into test values('33112244 5566778899'); insert into test values('44112233 5566 778899'); insert into test values('5511 2233 4466778899'); insert into test values('661122334455778899'); insert into test values('771122334455668899'); insert into test values('881122334455667799'); insert into test values('991122334455667788'); insert into test values('aabbccddee'); insert into test values('bbaaaccddee'); insert into test values('ccabbddee'); insert into test values('ddaabbccee'); insert into test values('eeaabbccdd'); insert into test values('ab123'); insert into test values('123xy'); insert into test values('007ab'); insert into test values('abcxy'); insert into test values('The final test is is is how to find duplicate words.');
REGEXP_LIKE
SQL> select * from test where regexp_like(mc,'^a{1,3}');
MC
------------------------------------------------------------
aabbccddee
ab123
Abcxy

查詢a開頭,並且a出現1次到三次的記錄.

REGEXP_LIKE
SQL> select * from test where regexp_like(mc,'a{1,3}');

MC
------------------------------------------------------------
aabbccddee
bbaaaccddee
ccabbddee
ddaabbccee
eeaabbccdd
ab123
007ab
abcxy
The final test is is is how to find duplicate words.
REGEXP_LIKE
SQL> select * from test where regexp_like(mc,'^a.*e$');

MC
------------------------------------------------------------
aabbccddee

查詢以a開頭,中間有零個或多個除換行符之外的任何單字元,以e結尾的記錄.

REGEXP_LIKE
SQL> select * from test where regexp_like(mc,'^[[:lower:]]');

MC
------------------------------------------------------------
aabbccddee
bbaaaccddee
ccabbddee
ddaabbccee
eeaabbccdd
ab123
Abcxy

查詢以小寫字母開頭的記錄.
REGEXP_LIKE
SQL> select * from test where regexp_like(mc,'^[[:lower:]]|[[:digit:]]');

MC
------------------------------------------------------------
112233445566778899
…..
ccabbddee
ddaabbccee
eeaabbccdd
ab123
123xy
007ab
abcxy
REGEXP_LIKE
SQL> Select mc FROM test Where REGEXP_LIKE(mc,'[^[:digit:]]');

MC
------------------------------------------------------------
22113344 5566778899
aabbccddee
bbaaaccddee
ccabbddee
ddaabbccee
eeaabbccdd
ab123
123xy
007ab
abcxy
The final test is is is how to find duplicate words.
查詢包含非數字的記錄
REGEXP_LIKE
SQL> Select mc FROM test Where REGEXP_LIKE(mc,'^[^[:digit:]]');

MC
------------------------------------------------------------
aabbccddee
bbaaaccddee
ccabbddee
ddaabbccee
eeaabbccdd
ab123
abcxy
The final test is is is how to find duplicate words.
REGEXP_INSTR
SQL> Select REGEXP_INSTR('aa453rer455daf554443','[[:digit:]]$') result from dual;

RESULT
----------
20

查以數字結尾字串編號.
REGEXP_INSTR
SQL> Select REGEXP_INSTR('aa453rer455daf554443','[[:digit:]]+$') result from dual;

RESULT
----------
15

查以多個數字結尾的字串編號.
REGEXP_INSTR
SQL> SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234','[[:digit:]]{5}$') AS rx_instr FROM dual;

RX_INSTR
----------
45

SQL> SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234','[[:digit:]]{5}') AS rx_instr FROM dual;

RX_INSTR
----------
12
REGEXP_SUBSTR
SQL> SELECT REGEXP_SUBSTR('11aa22BB33cc44','[a-z]+') result FROM dual;
RESULT
------
aa
SQL> SELECT REGEXP_SUBSTR('11aa22BB33cc44','[a-z]+',3,2) result FROM dual;

RESULT
------
cc
REGEXP_SUBSTR
SQL> SELECT REGEXP_SUBSTR('NE-19225 (01-Oct-2002)','[[:digit:]]{2}-[[:alpha:]]{3}-[[:digit:]]{4}') AS rx_instr FROM dual;

RX_INSTR
-----------
01-Oct-2002

SQL> SELECT REGEXP_SUBSTR('DX-sd4533 (20060203)','[[:digit:]]{8}') AS rx_instr FROM dual;

RX_INSTR
--------
20060203
REGEXP_SUBSTR
SQL> SELECT REGEXP_SUBSTR('The final test is is the implementation','([[:alnum:]]+)([[:space:]]+)1') AS substr FROM dual;

SUBSTR
------
is is

SQL> SELECT REGEXP_REPLACE('The final test is is the implementation','([[:alnum:]]+)([[:space:]]+)1','1') AS substr FROM dual;

SUBSTR
------------------------------------
The final test is the implementation
REGEXP_REPLACE
SQL> SELECT REPLACE('Joe Smith',' ', ' ') AS replace FROM dual;

REPLACE
----------
Joe Smith

SQL> SELECT REGEXP_REPLACE('Joe Smith', '( ){2,}', ' ') AS RX_REPLACE FROM dual;

RX_REPLACE
----------
Joe Smith
REGEXP_REPLACE
SQL> SELECT REGEXP_REPLACE ('

aaa', ']+>') result from dual;

RESULT
------
aaa
SQL> SELECT REGEXP_REPLACE(REGEXP_REPLACE ('NE-19225 (01-Oct-2002)', ' .*'),'.*-') result from dual;

RESULT
------
19225
REGEXP_REPLACE
SQL> select REGEXP_REPLACE('AndyChen','([[:lower:]])([[:upper:]])','1 2') result from dual;

RESULT
---------
Andy Chen
Using with DDL
CREATE TABLE t1 (
c1 VARCHAR2(20), CHECK
(REGEXP_LIKE(c1, '^[[:alpha:]]+$')));
INSERT INTO t1 VALUES ('newuser');
-> 1 row created.
INSERT INTO t1 VALUES ('newuser1');
-> ORA-02290: check constraint violated
INSERT INTO t1 VALUES ('new-user');
-> ORA-02290: check constraint violated

Using with DDL
CREATE INDEX t1_ind ON t1 (REGEXP_SUBSTR(c1, 'a'));

SELECT c1
FROM t1
WHERE REGEXP_SUBSTR(c1, 'a') = 'a';