Oracle 正则表达式

[abc] 将匹配一个单字符，a、b或c
[a-z] 将匹配从a到z的任一字符

[^a-z] 将匹配不属于a-z的任一字符

REGEXP_LIKE(source_char, pattern[, match_param ])

SELECT first_name, last_name
FROM hr.employees
WHERE REGEXP_LIKE(first_name, '^Ste(v|ph)en$')
ORDER BY first_name, last_name;

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Stephen              Stiles
Steven               King
Steven               Markle

SELECT first_name, last_name
FROM hr.employees
WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i')
ORDER BY last_name;

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Harrison             Bloom
Lex                  De Haan
Kevin                Feeney
Ki                   Gee
Nancy                Greenberg
Danielle             Greene
Alexander            Khoo
David                Lee

说明：  反向引用 "\1"

REGEXP_INSTR(source_char, pattern[, position[, occurrence[, return_opt[, match_param[, subexpr]]]]]

select
 regexp_instr('192.168.0.1','\d',1,level) ind
from dual
connect by level <= 9;

       IND
----------
         1
         2
         3
         5
         6
         7
         9
        11
         0   -- 没有匹配到时返回为0 

REGEXP_REPLACE(source_char, pattern[, replace_string[,position[, occurrence[, match_param ]]]])

select
  phone_number,
  regexp_replace(phone_number, '(\d{3})\.(\d{3})\.(\d{4})', '(\1) \2-\3') "REGEXP_REPLACE"
from hr.employees where rownum <= 5
order by "REGEXP_REPLACE";

PHONE_NUMBER         REGEXP_REPLACE
-------------------- --------------------
515.123.4444         (515) 123-4444
515.123.5555         (515) 123-5555
603.123.6666         (603) 123-6666
650.507.9833         (650) 507-9833
650.507.9844         (650) 507-9844

select
  country_name,
  regexp_replace(country_name, '(.)', '\1 ') "REGEXP_REPLACE"
from hr.countries
where rownum <= 5;

COUNTRY_NAME                             REGEXP_REPLACE
---------------------------------------- --------------------
Argentina                                A r g e n t i n a
Australia                                A u s t r a l i a
Belgium                                  B e l g i u m
Brazil                                   B r a z i l
Canada                                   C a n a d a

select
  regexp_replace('500   Oracle     Parkway,    Redwood  Shores, CA', '( ){2,}', ' ') "REGEXP_REPLACE"
from dual;

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

REGEXP_SUBSTR(source_char, pattern[, position[, occurrence[, match_param[, subexpr]]]])

select
  regexp_substr('500 Oracle Parkway, Redwood Shores, CA', ',[^,]+,') "REGEXPR_SUBSTR"
from dual;

REGEXPR_SUBSTR
-----------------
, Redwood Shores,

select
  regexp_substr('http://www.example.com/products', 'http://([[:alnum:]]+\.?){3,4}/?') "REGEXP_SUBSTR"
from dual;

REGEXP_SUBSTR
-----------------------
http://www.example.com/

REGEXP_COUNT(source_char, pattern [, position [, match_param]])

select 
  regexp_count('123123123123123', '(12)3', 1, 'i') "REGEXP_COUNT" from dual;

REGEXP_COUNT
------------
           5  

select 
  regexp_count('123123123123', '123', 3, 'i') "COUNT" 
from dual; 

     COUNT
----------
         3

select 
  regexp_replace('Susan Mavris', '(.*) (.*)', '\2, \1') "REVERSED_NAME" from dual; 

REVERSED_NAME
-------------
Mavris, Susan