ITPub博客

首页 > 数据库 > Oracle > Oracle REGEXP

Oracle REGEXP

原创 Oracle 作者:zhyuh 时间:2012-02-01 14:16:26 0 删除 编辑
REGEXP_LIKE
REGEXP_SUBSTR
REGEXP_INSTR
REGEXP_REPLACE
[@more@]====================
REGEXP_LIKE
====================
SQL> select * from t1;

COL1 COL2
---------- ----------
1 fly
2 flying
3 swim
4 Fly
5 Flies

SQL> select * from t1 where regexp_like(col2, 'fl(y(ing)?|(ies))');

COL1 COL2
---------- ----------
1 fly
2 flying

--'i' => 不区分大小写
SQL> select * from t1 where regexp_like(col2, 'fl(y(ing)?|(ies))','i');

COL1 COL2
---------- ----------
1 fly
2 flying
4 Fly
5 Flies



====================
REGEXP_SUBSTR
====================
REGEXP_SUBSTR(source_char,pattern,position,occurrence,match_parameter)
source_char=>源字符串
pattern=>正则表达式
position=>从source_char的第几个字符开始搜索,默认值1
occurrence=>返回第几次匹配结果,默认值1
match_parameter=>
'i'-case-insensitive
'c'-case-sensitive
'n'-allows the period (.), which is the match-any-character character, to match the newline character
'm'-treats the source string as multiple lines
'x'-ignores whitespace characters

SQL> select regexp_substr('Fly flying flies to Hangzhou flight Flies', 'fl(y(ing)?|(ies))') from dual;

REGEXP_SUBSTR('FLYFLYINGFLIEST
------------------------------
flying

SQL> select regexp_substr('Fly flying flies to Hangzhou flight Flies', 'fl(y(ing)?|(ies))',10) from

dual;

REGEXP_SUBSTR('FLYFLYINGFLIEST
------------------------------
flies

SQL> select regexp_substr('Fly flying flies to Hangzhou flight Flies', 'fl(y(ing)?|(ies))',10,2) from

dual;

REGEXP_SUBSTR('FLYFLYINGFLIEST
------------------------------

SQL> select regexp_substr('Fly flying flies to Hangzhou flight Flies', 'fl(y(ing)?|(ies))',10,2,'i')

from dual;

REGEXP_SUBSTR('FLYFLYINGFLIEST
------------------------------
Flies



====================
REGEXP_INSTR
====================
REGEXP_INSTR(source_char,pattern,position,occurrence,return_option,match_parameter)
source_char=>源字符串
pattern=>正则表达式
position=>从source_char的第几个字符开始搜索,默认值1
occurrence=>返回第几次匹配结果,默认值1
return_option=>
0-returns the position of the first character of the occurrence. Default
1-returns the position of the character following the occurrence
match_parameter=>
'i'-case-insensitive
'c'-case-sensitive
'n'-allows the period (.), which is the match-any-character character, to match the newline character
'm'-treats the source string as multiple lines
'x'-ignores whitespace characters

SQL> SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[s|r|p]', 3, 2, 1, 'i')

"REGEXP_INSTR" FROM DUAL;

REGEXP_INSTR
------------
13

SQL> SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA', '[s|r|p]', 3, 2, 0, 'i')

"REGEXP_INSTR" FROM DUAL;

REGEXP_INSTR
------------
12


====================
REGEXP_REPLACE
====================
REGEXP_REPLACE(source_char,pattern,replace_string,position,occurrence,match_parameter)

SQL> SELECT REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA', '( ){2,}', ' ') "REGEXP_REPLACE" FROM DUAL;

REGEXP_REPLACE
--------------------------------------
500 Oracle Parkway, Redwood Shores, CA

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-1057245/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2009568