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

Oracle REGEXP

相關文章