4  正则表达式函数

本节介绍正则表达式及相关的Oracle数据库函数。使用这些函数可以在字符串中搜索字符模式。例如，假设有下列年份：

1965

1968

1971

1970

如果希望获得1965年和1968年之间的年份(包括1965年和1968年)，就可以使用下面的正则表达式实现这种功能：

^196[5-8]$

正则表达式中包含许多元字符(metacharacter)。在上面这个例子中，^、[5-8]以及$都是元字符。^可以匹配一个字符串的开头；[5-8]可以匹配介于5~8之间的数字；$可以匹配一个字符串的结尾。因此，^196可以匹配以196开头的字符串；[5-8]$可以匹配以5、6、7或8结尾的字符串。而^196[5-8]$就可以匹配1965、1966、1967和1968，这就是想要的结果。

在下面这个例子中，假设有如下字符串，其中引用了莎士比亚的《罗密欧与朱丽叶》中的一句台词：

But, soft! What light through yonder window breaks?

如果想查找子字符串light，可以对引用的字符串应用下面的正则表达式：

l[[:alpha:]]{4}

在这个例子中，[[:alpha:]]和{4}都是元字符。[[:alpha:]]可以匹配A-Z或a-z之间的字符；{4}表示前面的匹配模式可以重复4次。当l、[[:alpha:]]和{4}一起使用时，可以匹配以 l 开头的5个字母组成的序列。因此，当对这个字符串应用正则表达式 l[[:alpha:]]{4}时，就可以匹配子字符串light。

表1-7列出了在正则表达式中可以使用的部分元字符，同时还给出了这些元字符的意思以及使用这些元字符的简单例子。

表1-7  正则表达式中的元字符

Oracle Database 10g Release 2新增加了很多类似于Perl的元字符，如表1-8所示。

表1-8  类似于Perl的元字符

表1-9列出了正则表达式函数。正则表达式函数是在Oracle Database 10g中新增加的， Oracle Database 11g中又增加了一些条目，如下表所示。

表1-9  正则表达式函数

接下来将会介绍更多有关正则表达式函数的知识。

1. REGEXP_LIKE()

REGEXP_LIKE(x, pattern [, match_option])用于在x中查找pattern参数中定义的正则表达式，该函数还可以提供一个可选参数match_option，它可以设置为下面几个字符之一：

● 'c'，说明在匹配时区分大小写(默认选项) 

● 'I'，说明在匹配时不区分大小写

● 'n'，允许使用可以匹配任意字符的操作符

● 'm'，将x 作为一个包含多行的字符串

下面这个查询使用REGEXP_LIKE函数检索生日在1965年到1968年之间的顾客：

SELECT customer_id, first_name, last_name, dob

FROM customers

WHERE REGEXP_LIKE(TO_CHAR(dob, 'YYYY'), '^196[5-8]$');

CUSTOMER_ID  FIRST_NAME  LAST_NAME   DOB

-----------  ----------  ----------  ---------

          1     John         Brown       01-JAN-65

          2     Cynthia      Green       05-FEB-68

下面这个查询检索名字以J或j开头的顾客。注意传递给REGEXP_LIKE()的正则表达式是 ^j，匹配选项是i，这说明不区分大小写，因此在本例中，^j 可以匹配J或j：

SELECT customer_id, first_name, last_name, dob

FROM customers

WHERE REGEXP_LIKE(first_name, '^j', 'i');

CUSTOMER_ID  FIRST_NAME  LAST_NAME  DOB

-----------  ----------  ---------- ---------

          1      John        Brown      01-JAN-65

2. REGEXP_INSTR()

REGEXP_INSTR(x, pattern [, start [, occurrence [, return_option [, match_option]]]])用于在x中查找pattern；REGEXP_INSTR()返回pattern出现的位置。匹配位置从1开始。

下面这个查询使用REGEXP_INSTR函数返回匹配正则表达式 l[[:alpha:]]{4}的位置：

SELECT  REGEXP_INSTR('But, soft! What light through yonder window breaks?',

'l[[:alpha:]]{4}') AS result

FROM dual;

RESULT

----------

17

注意返回值为17，这是light中l的位置。

下面这个查询返回第二次匹配正则表达式 s[[:alpha:]]{3}的位置，匹配位置从1开始：

SELECT

REGEXP_INSTR('But, soft! What light through yonder window softly breaks?',

's[[:alpha:]]{3}', 1, 2) AS result

FROM dual;

RESULT

----------

45

下面这个查询使用REGEXP_INSTR函数返回第二次匹配字母 o 的位置，匹配位置从10开始：

SELECT

REGEXP_INSTR('But, soft! What light through yonder window breaks?',

'o', 10, 2) AS result

FROM dual;

RESULT

----------

32

3. REGEXP_REPLACE()

REGEXP_REPLACE(x, pattern [, replace_string [, start [, occurrence[, match_option]]]])用于在x中查找pattern，并将其替换为 replace_string。

下面这个查询使用REGEXP_REPLACE函数将匹配正则表达式 l[[:alpha:]]{4}的子字符串替换为字符串 sound：

SELECT

REGEXP_REPLACE('But, soft! What light through yonder window breaks?',

'l[[:alpha:]]{4}', 'sound') AS result

FROM dual;

RESULT

---------------------------------------------------

But, soft! What sound through yonder window breaks?

注意light已经被替换为sound。

4. REGEXP_SUBSTR()

REGEXP_SUBSTR(x, pattern[, start [, occurrence[, match_option]]])用于在x中查找匹配pattern的子字符串，开始位置由 start指定。

下面这个查询使用REGEXP_SUBSTR函数返回匹配正则表达式 l[[:alpha:]]{4}的子字符串：

SELECT

REGEXP_SUBSTR('But, soft! What light through yonder window breaks?',

'l[[:alpha:]]{4}') AS result

FROM dual;

RESUL

-----

light

5. REGEXP_COUNT()

REGEXP_COUNT()是Oracle Database 11g新增加的一个函数。REGEXP_COUNT(x, pattern[, start [,match_option]])用于在x中查找pattern，并返回pattern在x中出现的次数。可以提供可选参数start，指出要从x中开始查找pattern的那个字符；也可以提供可选的match_option字符串，指出匹配选项。

下面这个查询使用REGEXP_COUNT函数返回正则表达式s[[:alpha:]]{3}出现的次数：

SELECT

    REGEXP_COUNT('But, soft! What light through yonder window softly breaks?',

    's[[:alpha:]]{3}') AS result

FROM dual;

    RESULT

--------

    2

注意返回结果是2，这表明正则表达式在提供的字符串中有两次匹配