Oracle10g -新特性- 正则表达式
关于正则表达式的基础介绍，可以参考《深入浅出之正则表达式》，Oracle10g中实现的正则表达式与之基本相同，详细内容可以参考《10g中的正则表达式》。Oracle10g提供了4个可以使用正则表达式的函数：
- regexp_like
- regexp_substr
- regexp_instr
- regexp_replace
在包sys.standard中可以看到这些函数的声明（见附录）。这里主要对这4个函数做一些小测试。
1. regexp_like
REGEXP_LIKE (srcstr, pattern, modifier )
__srcstr ：检索字符串
__pattern ：匹配模式
__modifier ：检索模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）
__return type ：boolean
eg:
|
SQL> select 1 from dual where regexp_like('Alibaba-13930471222','^alibaba-[139|135|152][0-9]+{8}$');
1
----------
SQL> select 1 from dual where regexp_like('Alibaba-13930471222','^alibaba-[139|135|152][0-9]+{8}$', 'c');
1
---------- SQL> select 1 from dual where regexp_like('Alibaba-13930471222','^alibaba-[139|135|152][0-9]+{8}$', 'i');
1
----------
1 |
2. regexp_substr
REGEXP_SUBSTR(srcstr, pattern, position, occurrence, modifier)
__srcstr ：检索字符串
__pattern ：匹配模式
__position ：搜索srcstr的起始位置（默认为1）
__occurrence：搜索第几次出现匹配模式的字符串（默认为1）
__modifier ：检索模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）
eg:
|
SQL> select regexp_substr('http://blog.chinaunix.net/u/30637/showart_1009927.html', '[0-9]+') from dual;
REGEXP_SUBSTR('HTTP://BLOG.CHI
------------------------------
30637 SQL> select regexp_substr('http://blog.chinaunix.net/u/30637/showart_1009927.html', '[0-9]+', 1) from dual;
REGEXP_SUBSTR('HTTP://BLOG.CHI
------------------------------
30637
SQL> select regexp_substr('http://blog.chinaunix.net/u/30637/showart_1009927.html', '[0-9]+', 35) from dual;
REGEXP_SUBSTR('HTTP://BLOG.CHI
------------------------------
1009927
SQL> select regexp_substr('http://blog.chinaunix.net/u/30637/showart_1009927.html', '[0-9]+', 1, 2) from dual;
REGEXP_SUBSTR('HTTP://BLOG.CHI
------------------------------
1009927
SQL> select regexp_substr('http://blog.chinaunix.net/u/30637/showart_1009927.html', '[a-z_0-9]+', 35) from dual;
REGEXP_SUBSTR('HTTP://BLOG.CHI
------------------------------
showart_1009927
SQL> select regexp_substr('http://blog.chinaunix.net/u/30637/Showart_1009927.html', '[a-z_0-9]+', 35) from dual;
REGEXP_SUBSTR('HTTP://BLOG.CHI
------------------------------
howart_1009927
SQL> select regexp_substr('http://blog.chinaunix.net/u/30637/Showart_1009927.html', '[a-z_0-9]+', 35, 1, 'c') from dual;
REGEXP_SUBSTR('HTTP://BLOG.CHI
------------------------------
howart_1009927 SQL> select regexp_substr('http://blog.chinaunix.net/u/30637/Showart_1009927.html', '[a-z_0-9]+', 35, 1, 'i') from dual;
REGEXP_SUBSTR('HTTP://BLOG.CHI
------------------------------
Showart_1009927 |
3. regexp_instr
REGEXP_INSTR(srcstr, pattern, position, occurrence, returnparam, modifier)
__srcstr ：检索字符串
__pattern ：匹配模式
__position ：搜索srcstr的起始位置（默认为1）
__occurrence ：搜索第几次出现匹配模式的字符串（默认为1）
__returnparam ：返回该子串在srcstr中的位置（0表示头位置，1表示尾位置+1，默认为0。）
__modifier ：检索模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）
|
SQL> select regexp_instr('http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9]+') from dual;
REGEXP_INSTR('HTTP://SHOP35741
------------------------------
12
SQL> select regexp_instr('http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9]+', 21) from dual;
REGEXP_INSTR('HTTP://SHOP35741
------------------------------
52
SQL> select regexp_instr('http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9]+', 1, 2) from dual;
REGEXP_INSTR('HTTP://SHOP35741
------------------------------
52
SQL> select regexp_instr('http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9]+', 1, 1, 0) from dual;
REGEXP_INSTR('HTTP://SHOP35741
------------------------------
12
SQL> select regexp_instr('http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9]+', 1, 1, 1) from dual;
REGEXP_INSTR('HTTP://SHOP35741
------------------------------
20
SQL> select regexp_instr('http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9]+', 1, 2, 0) from dual;
REGEXP_INSTR('HTTP://SHOP35741
------------------------------
52
SQL> select regexp_instr('http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[0-9]+', 1, 2, 1) from dual;
REGEXP_INSTR('HTTP://SHOP35741
------------------------------
60
SQL> select regexp_instr('http://shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[a-z]*[0-9]+', 1, 1, 0) from dual;
REGEXP_INSTR('HTTP://SHOP35741
------------------------------
8
SQL> select regexp_instr('http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[a-z]*[0-9]+', 1, 1, 0) from dual;
REGEXP_INSTR('HTTP://SHOP35741
------------------------------
9
SQL> select regexp_instr('http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[a-z]*[0-9]+', 1, 1, 0, 'i') from dual;
REGEXP_INSTR('HTTP://SHOP35741
------------------------------
8
SQL> select regexp_instr('http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[a-z]*[0-9]+', 1, 1, 0, 'c') from dual;
REGEXP_INSTR('HTTP://SHOP35741
------------------------------
9 |
4. regexp_replace
function REGEXP_REPLACE(srcstr, pattern, replacestr, position, occurrence, modifier)
__srcstr ：检索字符串
__pattern ：匹配模式
__replacestr ：新的子串（默认值为NULL）
__position ：srcstr的检索起始位置（默认为1）
__occurrence ：替换第几次出现匹配模式的字符串（默认为0）
__modifier ：检索模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）
|
SQL> select regexp_replace('http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[a-z]*[0-9]+', 'YCT') from dual;
REGEXP_REPLACE('HTTP://SHOP357
--------------------------------------------------
http://SYCT.taobao.com/shop/xshop/wui_page-YCT.htm
SQL> select regexp_replace('http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[a-z]*[0-9]+', 'YCT', 20) from dual;
REGEXP_REPLACE('HTTP://SHOP357
----------------------------------------------------------
http://Shop35741645.taobao.com/shop/xshop/wui_page-YCT.htm
SQL> select regexp_replace('http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[a-z]*[0-9]+', 'YCT', 1, 2) from dual;
REGEXP_REPLACE('HTTP://SHOP357
----------------------------------------------------------
http://Shop35741645.taobao.com/shop/xshop/wui_page-YCT.htm
SQL> select regexp_replace('http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[a-z]*[0-9]+', 'YCT', 1, 1) from dual;
REGEXP_REPLACE('HTTP://SHOP357
-------------------------------------------------------
http://SYCT.taobao.com/shop/xshop/wui_page-16115021.htm
SQL> select regexp_replace('http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[a-z]*[0-9]+', 'YCT', 1, 1, 'i') from dual;
REGEXP_REPLACE('HTTP://SHOP357
------------------------------------------------------
http://YCT.taobao.com/shop/xshop/wui_page-16115021.htm
SQL> select regexp_replace('http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[a-z]*([0-9])+', '\1', 1, 1, 'i') from dual;
REGEXP_REPLACE('HTTP://SHOP357
----------------------------------------------------
http://5.taobao.com/shop/xshop/wui_page-16115021.htm
SQL> select regexp_replace('http://Shop35741645.taobao.com/shop/xshop/wui_page-16115021.htm', '[a-z]*([0-9]+)', '\1', 1, 1, 'i') from dual;
REGEXP_REPLACE('HTTP://SHOP357
-----------------------------------------------------------
http://35741645.taobao.com/shop/xshop/wui_page-16115021.htm |
5. 附录
|
-- REGEXP_LIKE --
function REGEXP_LIKE (srcstr VARCHAR2 CHARACTER SET ANY_CS,
pattern VARCHAR2 CHARACTER SET srcstr%CHARSET,
modifier VARCHAR2 DEFAULT NULL)
return BOOLEAN;
pragma FIPSFLAG('REGEXP_LIKE', 1452);
function REGEXP_LIKE (srcstr CLOB CHARACTER SET ANY_CS,
pattern VARCHAR2 CHARACTER SET srcstr%CHARSET,
modifier VARCHAR2 DEFAULT NULL)
return BOOLEAN;
pragma FIPSFLAG('REGEXP_LIKE', 1452);
-- REGEXP_INSTR --
function REGEXP_INSTR(srcstr VARCHAR2 CHARACTER SET ANY_CS,
pattern VARCHAR2 CHARACTER SET srcstr%CHARSET,
position PLS_INTEGER := 1,
occurrence PLS_INTEGER := 1,
returnparam PLS_INTEGER := 0,
modifier VARCHAR2 DEFAULT NULL)
return PLS_INTEGER;
pragma FIPSFLAG('REGEXP_INSTR', 1452);
function REGEXP_INSTR(srcstr CLOB CHARACTER SET ANY_CS,
pattern VARCHAR2 CHARACTER SET srcstr%CHARSET,
position INTEGER := 1,
occurrence INTEGER := 1,
returnparam PLS_INTEGER := 0,
modifier VARCHAR2 DEFAULT NULL)
return INTEGER;
pragma FIPSFLAG('REGEXP_INSTR', 1452);
-- REGEXP_SUBSTR --
function REGEXP_SUBSTR(srcstr VARCHAR2 CHARACTER SET ANY_CS,
pattern VARCHAR2 CHARACTER SET srcstr%CHARSET,
position PLS_INTEGER := 1,
occurrence PLS_INTEGER := 1,
modifier VARCHAR2 DEFAULT NULL)
return VARCHAR2 CHARACTER SET srcstr%CHARSET;
pragma FIPSFLAG('REGEXP_SUBSTR', 1452);
function REGEXP_SUBSTR(srcstr CLOB CHARACTER SET ANY_CS,
pattern VARCHAR2 CHARACTER SET srcstr%CHARSET,
position INTEGER := 1,
occurrence INTEGER := 1,
modifier VARCHAR2 DEFAULT NULL)
return CLOB CHARACTER SET srcstr%CHARSET;
pragma FIPSFLAG('REGEXP_SUBSTR', 1452);
-- REGEXP_REPLACE --
function REGEXP_REPLACE(srcstr VARCHAR2 CHARACTER SET ANY_CS,
pattern VARCHAR2 CHARACTER SET srcstr%CHARSET,
replacestr VARCHAR2 CHARACTER SET srcstr%CHARSET
DEFAULT NULL,
position PLS_INTEGER := 1,
occurrence PLS_INTEGER := 0,
modifier VARCHAR2 DEFAULT NULL)
return VARCHAR2 CHARACTER SET srcstr%CHARSET;
pragma FIPSFLAG('REGEXP_REPLACE', 1452);
function REGEXP_REPLACE(srcstr CLOB CHARACTER SET ANY_CS,
pattern VARCHAR2 CHARACTER SET srcstr%CHARSET,
replacestr CLOB CHARACTER SET srcstr%CHARSET
DEFAULT NULL,
position INTEGER := 1,
occurrence INTEGER := 0,
modifier VARCHAR2 DEFAULT NULL)
return CLOB CHARACTER SET srcstr%CHARSET;
pragma FIPSFLAG('REGEXP_REPLACE', 1452);
function REGEXP_REPLACE(srcstr CLOB CHARACTER SET ANY_CS,
pattern VARCHAR2 CHARACTER SET srcstr%CHARSET,
replacestr VARCHAR2 CHARACTER SET srcstr%CHARSET
DEFAULT NULL,
position INTEGER := 1,
occurrence INTEGER := 0,
modifier VARCHAR2 DEFAULT NULL)
return CLOB CHARACTER SET srcstr%CHARSET;
pragma FIPSFLAG('REGEXP_REPLACE', 1452);
-- End REGEXP Support -- |
作者：全哥
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
