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博客

Oracle REGEXP

最新文章