Oracle正則表達式

　　Oracle g數據庫內建了符合IEEE POSIX (Portable Operating System for Unix)標准的正則表達式熟練使用正則表達式可以寫出簡潔強大的SQL語句

　　正則表達式有幾個優點優於常見的LIKE操作符和INSTRSUBSTR及REPLACE 函數的這些傳統的SQL 函數不便於進行模式匹配只有LIKE 操作符通過使用%和_字符匹配但LIKE不支持表達式的重復復雜的更替字符范圍字符列表和POSIX 字符類等等

　　元字符（Meta Character）

　　Sql代碼

　　^            使表達式定位至一行的開頭

　　$            使表達式定位至一行的末尾

　　*            匹配 次或更多次

　　?            匹配 次或 次

　　+            匹配 次或更多次

　　{m}          正好匹配 m 次

　　{m}         至少匹配 m 次

　　{m n}       至少匹配 m 次但不超過 n 次

　　[:alpha:]    字母字符

　　[:lower:]    小寫字母字符

　　[:upper:]    大寫字母字符

　　[:digit:]    數字

　　[:alnum:]    字母數字字符

　　[:space:]    空白字符（禁止打印）如回車符換行符豎直制表符和換頁符[:punct:]    標點字符

　　[:cntrl:]    控制字符（禁止打印）

　　[:print:]    可打印字符 | 分隔替換選項通常與分組操作符 () 一起使用

　　( )          將子表達式分組為一個替換單元量詞單元或後向引用單元

　　[char]       字符列表

　　Oracle g提供了四個regexp function: REGEXP_LIKE REGEXP_REPLACE REGEXP_INSTR REGEXP_SUBSTR

　　Sql代碼

　　REGEXP_LIKE比較一個字符串是否與正則表達式匹配

　　(srcstr pattern [ match_option])

　　REGEXP_INSTR在字符串中查找正則表達式並且返回匹配的位置

　　(srcstr pattern [ position [ occurrence [ return_option [ match_option]]]])

　　REGEXP_SUBSTR返回與正則表達式匹配的子字符串

　　(srcstr pattern [ position [ occurrence [ match_option]]])

　　REGEXP_REPLACE搜索並且替換匹配的正則表達式

　　(srcstr pattern [ replacestr [ position [ occurrence [ match_option]]]])  其中各參數的含義為:

　　Sql代碼

　　srcstr:        被查找的字符數據

　　pattern:       正則表達式

　　occurrence:    出現的次數默認為

　　position:      開始位置

　　return_option: 默認值為返回該模式的起始位置值為則返回符合匹配條件的下一個字符的起始位置

　　replacestr:    用來替換匹配模式的字符串

　　match_option:  匹配方式選項缺省為c

　　ccase sensitive

　　Icase insensitive

　　n()匹配任何字符（包括newline)

　　m字符串存在換行的時候被作為多行處理

　　下面通過一些具體的例子來說明如何使用這四個函數首先創建一個測試數據表

　　Sql代碼

　　SQL> create table person (

　　  first_name varchar()

　　  last_name varchar()

　　  email varchar()

　　  zip varchar());

　　Table created

　　SQL> insert into person values (Steven Chen );

　　 row created

　　SQL> insert into person values (James Li || chr() || bdf);

　　 row created

　　SQL> commit;

　　Commit complete

　　SQL> select * from person;

　　FIRST_NAME LAST_NAME  EMAIL                ZIP

　　

　　Steven     Chen              

　　James      Li               bdf

　　

　　REGEXP_LIKE

　　Sql代碼

　　SQL> select zip as invalid_zip from person where regexp_like(zip [^[:digit:]]);

　　INVALID_ZIP

　　

　　bdf

　　SQL> select first_name from person where regexp_like(first_name ^S*n$);

　　FIRST_NAME

　　

　　Steven

　　SQL> select first_name from person where regexp_like(first_name ^s*n$);

　　no rows selected

　　SQL> select first_name from person where regexp_like(first_name ^s*n$ c);

　　no rows selected

　　SQL> select first_name from person where regexp_like(first_name ^s*n$ i);

　　FIRST_NAME

　　

　　Steven

　　SQL> select email from person where regexp_like(email ^james*com$);

　　no rows selected

　　SQL> select email from person where regexp_like(email ^james*com$ n);

　　EMAIL

　　

　　

　　

　　SQL> select email from person where regexp_like(email ^li*com$);

　　no rows selected

　　SQL> select email from person where regexp_like(email ^li*com$ m);

　　EMAIL

　　

　　

　　

　　REGEXP_INSTR

　　Sql代碼

　　查找zip中第一個非數字字符的位置

　　SQL> select regexp_instr(zip [^[:digit:]]) as position from person;

　　POSITION

　　

　　

　　

　　從第三個字符開始查找zip中第二個非數字字符的位置

　　SQL> select regexp_instr(zip [^[:digit:]] ) as position from person;

　　POSITION

　　

　　

　　

　　從第三個字符開始查找zip中第二個非數字字符的下一個字符位置

　　SQL> select regexp_instr(zip [^[:digit:]] ) as position from person;

　　POSITION

　　

　　

　　

　　REGEXP_SUBSTR

　　Sql代碼

　　SQL> select regexp_substr(zip [^[:digit:]]) as zip from person;

　　ZIP

　　

　　b

　　SQL> select regexp_substr(zip [^[:digit:]] ) as zip from person;

　　ZIP

　　

　　f

　　REGEXP_REPLACE

　　Sql代碼

　　把zip中所有非數字字符替換為

　　SQL> update person set zip=regexp_replace(zip [^[:digit:]] )

　　  where regexp_like(zip [^[:digit:]]);

　　 row updated

　　SQL> select zip from person;

　　ZIP

　　

　　

　　

　　後向引用（backreference）

　　後向引用是 一個很有用的特性它能夠把子表達式的匹配部分保存在臨時緩沖區中供以後重用 緩沖區從左至右進行編號並利用 \digit 符號進行訪問子表達式用一組圓括號來顯示利用後向引用可以實現較復雜的替換功能

　　Sql代碼

　　SQL> select regexp_replace(Steven Chen (*) (*) \ \) as reversed_name from dual;

　　REVERSED_NAME

　　

　　Chen Steven

　　在DDL中也可以正則表達式比如Constraint index view

　　Sql代碼

　　SQL> alter table person add constraint constraint_zip check (regexp_like(zip ^[[:digit:]]+$));

　　SQL> create index person_idx on person(regexp_substr(last_name ^[[:upper:]]));

From:http://tw.wingwit.com/Article/program/Oracle/201311/18745.html