ORACLE正则表达式函数详解

Oracle 10g提供了四个正则表达式函数regexp function: REGEXP_LIKE, REGEXP_REPLACE,REGEXP_INSTR,REGEXP_SUBSTR。

REGEXP_LIKE：(匹配) 比较一个字符串是否与正则表达式匹配

(srcstr, pattern [, match_option])

REGEXP_INSTR：(包含)在字符串中查找正则表达式，并且返回匹配的位置

(srcstr, pattern [, position [, occurrence [, return_option [, match_option]]]])

REGEXP_SUBSTR：(提取) 返回与正则表达式匹配的子字符串

(srcstr, pattern [, position [, occurrence [, match_option]]])

REGEXP_REPLACE：(替换)搜索并且替换匹配的正则表达式

(srcstr, pattern [, replacestr [, position [, occurrence [, match_option]]]])

srcstr: 被查找的字符数据。

pattern: 正则表达式。

occurrence: 出现的次数。默认为1。

position: 开始位置

return_option: 默认值为0，返回该模式的起始位置；值为1则返回符合匹配条件的下一个字符的起始位置。

replacestr: 用来替换匹配模式的字符串。

match_option: 匹配方式选项。缺省为c。

c：case sensitive

I：case insensitive

n：(.)匹配任何字符（包括newline)

m：字符串存在换行的时候被作为多行处理

##################################################################################

正则表达式由以下标准的元字符（metacharacters）所构成：

字符

[[:alpha:]]任何字母。[A-Za-z]

[[:digit:]]任何数字。[0-9]

[[:alnum:]]任何字母和数字。[A-Za-z0-9]

[:blank:] [ ]，标准是空格和TAB，但Oracle里只有空格！

[:cntrl:] CONTROL键

[:graph:] 即所有可见字符,不包括空格

[:print:] 可打印字符 ,即空格＋所有可见字符

[[:space:]] 空白字符。

[[:punct:]] 任何标点符号。

[[:upper:]] 任何大写字母。[A-Z]

[[:lower:]] 任何小写字母。[a-z]

[[:xdigit:]]任何16进制的数字，相当于[0-9a-fA-F]。

#######

[:class:] 字符类。class如alnum、digit等。

[.element.] 整理类。受NLS_SORT影响。将多字节字符当作单字节字符看待。如在西班牙语中ch、ll都是用多字节表示的单字节。

[=char=] 等同类。在当前语言（Locale）下，匹配所有的属于相同的POSIX等同类字符指定的字符。如在西班牙语中，[[=n=]]指定了等同类字符n，它可以匹配El Ni?o中的N和 ?.

###########################################################################

边界符集合

^ 每一行的开头，单行模式下等价于字符串的开头

$ 每一行的结尾，单行模式下等价于字符串的结尾

在单行模式下，^和\A等价，$和\Z等价。

##########################################################################

重复次数集合

* 匹配零次或多次--尽可能多的次数

? 零次或一次

+ 一次或多次

{m} {m}?正好m次，贪婪与非贪婪一样的

{m,} {m,}?至少m次

{m, n} {m, n}?最少m最多n次

在上述字符后加？，如：*?，即为非贪婪模式。

贪婪模式会获取尽可能多的字符，而非贪婪模式会获取尽可能少的字符

###############################################################

组合操作符

[…] 方括号内任意字符或字符集合中的一个。在其中所有的操作符号都被视为普通符号，但以下除外：

★范围操作符：- 但当"-"位于字符列表的第一个或最后一个字符时，或位于范围的结尾时，会被当作"-"本身来看待。

当右方括号"]"出现在列表的第一个位置时，它会被当作"]"本身来处理。

[^…] 方括号内^为第一个字符时，表示与其后所有字符都不匹配的字符

(…) 圆括号，将复杂表达式当作单一表达式来处理

..|.. 或

abc 和。直接将字符连在一起写

优先级比较：圆括号>重复次数操作符>和>或。

例如，(f|ht)tps?: 表示

ftp:

ftps:

http:

https:

######################################################################

匹配操作符

\n 即后向引用。n为1~9，标识由圆括号里取得的匹配字符串。方向是从左到右。

转义操作符

\ 将其后紧跟着的操作字符当作普通字符看待。

例如 abc*def 可以匹配 abdef或abcccdef等，但无法匹配abc*def，后者需要abc\*def才能匹配

##################################################

一条正则表达式匹配函数示例解析：

SELECT * FORM tKHXX where REGEXP_LIKE(SJHM, '^[1]{1}[35]{1}[[:digit:]]{9}$' )

这个语句是从表中查手机号。

^ 表示开始

$ 表示结束

[]内部为匹配范围

{}里的内容表时个数

手机号码的特点是以 1开头接着是3或5再加9位的数字所以这么理解

1开头表达式为 ^[1]{1} 意为开始1位里包含1

3或5 表达式为 [35]{1}，即第二位包含有3或5的

9位数字结束为: [[:digit:]]{9}$ 这里[:digit:]为特殊写法，代表为数字，再加个结束符$。即最后9位是数字。

#######################################################################

后向引用（backreference）：

后向引用是一个很有用的特性。它能够把子表达式的匹配部分保存在临时缓冲区中，供以后重用。缓冲区从左至右进行编号，并利用 \digit 符号进行访问。子表达式用一组圆括号来显示。利用后向引用可以实现较复杂的替换功能。

下面的regexp_replace('Steven Chen', '(.*) (.*)', '\2, \1') 此函数中，首先在正则表达式函数中，('Steven Chen', '(.*) (.*)', 其中两个圆括号中会匹配出Chen, Steven；再使用"\数字“的符号进行引用。

    SQL> select regexp_replace('Steven Chen', '(.*) (.*)', '\2, \1') as reversed_name from dual;        
    REVERSED_NAME  
    --------------------  
    Chen, Steven  
在DDL中也可以正则表达式，比如Constraint, index, view
Sql代码  
    SQL> alter table person add constraint constraint_zip check (regexp_like(zip, '^[[:digit:]]+$'));  
      
    SQL> create index person_idx on person(regexp_substr(last_name, '^[[:upper:]]'));