SQL基础（十三）正则表达式

ORACLE 10g新增了正则表达式 REGEXP_LIKE,REGEXP_INSTR,REGEXP_SUBSTR,REGEXP_REPLACE[@more@]

■REGEXP_LIKE模糊匹配操作符
REGEXP_LIKE(source_string, pattern[, match_parameter])
source_string 支持字符数据类型（CHAR、VARCHAR2、CLOB、NCHAR、NVARCHAR2 和 NCLOB，但不包括 LONG）。
pattern 参数是正则表达式的另一个名称。match_parameter 允许可选的参数（如处理换行符、保留多行格式化
以及提供对区分大小写的控制）。

检索 tTest 表中的那些 code 列值包含了任意非数字字符的行。
SELECT code
FROM tTest
WHERE REGEXP_LIKE(code, '[^[:digit:]]');

■REGEXP_INSTR返回一个模式的起始位置函数
REGEXP_INSTR(source_string, pattern[, start_position[, occurrence
[, return_option[, match_parameter]]]])
该函数查找 pattern ，并返回该模式的第一个位置。您可以随意指定您想要开始搜索的 start_position。
occurrence 参数默认为 1，除非您指定您要查找接下来出现的一个模式。return_option 的默认值为 0，
它返回该模式的起始位置；值为 1 则返回符合匹配条件的下一个字符的起始位置。

返回字符串 Joe Smith, 10045 Berry Lane, San Joseph, CA 91234 中的五位邮政编码模式的起始位置。
如果正规表达式被写为 [[:digit:]]{5}，则您将得到门牌号的起始位置而不是邮政编码的，
因为 10045 是第一次出现五个连续数字。因此，您必须将表达式定位到该行的末尾，正如 $ 元字符所示，
该函数将显示邮政编码的起始位置，而不管门牌号的数字个数。
SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234','[[:digit:]]{5}$') AS firstposition
FROM dual;

■REGEXP_SUBSTR截取字符串函数
REGEXP_SUBSTR(source_string, pattern[, position [, occurrence[, match_parameter]]])
REGEXP_SUBSTR 函数返回匹配模式的子字符串。

在下面的示例中，匹配模式 [^,]* 的字符串将被返回。该正规表达式搜索其后紧跟着空格的一个逗号；
然后按 [^,]* 的指示搜索零个或更多个不是逗号的字符，最后查找另一个逗号。这种模式看起来有点像
一个用逗号分隔的值字符串。

SELECT REGEXP_SUBSTR('first field, second field , third field',', [^,]*,') substr
FROM dual;

■REGEXP_REPLACE替换函数
REGEXP_REPLACE(source_string, pattern[, replace_string [, position[,occurrence, [match_parameter]]]])
该函数用一个指定的 replace_string 来替换匹配的模式，从而允许复杂的“搜索并替换”操作。

以下查询用单个空格替换了任意两个或更多的空格。( ) 子表达式包含了单个空格，它可以按 {2,} 的指示重复两次或更多次。
SELECT REGEXP_REPLACE('Joe Smith','( ){2,}', ' ') AS onspace
FROM dual;

■后向引用

正则表达式的一个有用的特性是能够存储子表达式供以后重用；这也被称为后向引用（在表 5 中对其进行了概述）。
它允许复杂的替换功能，如在新的位置上交换模式或显示重复出现的单词或字母。子表达式的匹配部分保存在临时
缓冲区中。缓冲区从左至右进行编号，并利用 digit 符号进行访问，其中 digit 是 1 到 9 之间的一个数字，
它匹配第 digit 个子表达式，子表达式用一组圆括号来显示。

接下来的例子显示了通过按编号引用各个子表达式将姓名 Ellen Hildi Smith 转变为 Smith, Ellen Hildi。
SELECT REGEXP_REPLACE('Ellen Hildi Smith','(.*) (.*) (.*)', '3, 1 2') FROM dual;

该 SQL 语句显示了用圆括号括住的三个单独的子表达式。每一个单独的子表达式包含一个匹配元字符 (.)，并紧跟着 *
元字符，表示任何字符（除换行符之外）都必须匹配零次或更多次。空格将各个子表达式分开，空格也必须匹配。圆括号
创建获取值的子表达式，并且可以用 digit 来引用。第一个子表达式被赋值为 1 ，第二个 2，以此类推。这些后向引用
被用在这个函数的最后一个参数 (3, 1 2) 中，这个函数有效地返回了替换子字符串，并按期望的格式来排列它们
（包括逗号和空格）。表 6 详细说明了该正则表达式的各个组成部分。

后向引用对替换、格式化和代替值非常有用，并且您可以用它们来查找相邻出现的值。接下来的例子显示了使用 REGEP_SUBSTR
函数来查找任意被空格隔开的重复出现的字母数字值。显示的结果给出了识别重复出现的单词 is 的子字符串。
SELECT REGEXP_SUBSTR('The final test is is the implementation','([[:alnum:]]+)([[:space:]]+)1') AS substr
FROM dual;

附录：

表 1：定位元字符

元字符	说明
^	使表达式定位至一行的开头
$	使表达式定位至一行的末尾

表 2：量词或重复操作符

量词	说明
*	匹配 0 次或更多次
?	匹配 0 次或 1 次
+	匹配 1 次或更多次
{m}	正好匹配 m 次
{m,}	至少匹配 m 次
{m, n}	至少匹配 m 次但不超过 n 次

表 3：预定义的 POSIX 字符类

字符类	说明
[:alpha:]	字母字符
[:lower:]	小写字母字符
[:upper:]	大写字母字符
[:digit:]	数字
[:alnum:]	字母数字字符
[:space:]	空白字符（禁止打印），如回车符、换行符、竖直制表符和换页符
[:punct:]	标点字符
[:cntrl:]	控制字符（禁止打印）
[:print:]	可打印字符

表 4：表达式的替换匹配和分组

元字符		说明
|	替换	分隔替换选项，通常与分组操作符 () 一起使用
( )	分组	将子表达式分组为一个替换单元、量词单元或后向引用单元（参见“后向引用”部分）
[char]	字符列表	表示一个字符列表；一个字符列表中的大多数元字符（除字符类、^ 和 - 元字符之外）被理解为文字

表 5：后向引用元字符

元字符		说明
digit	反斜线	紧跟着一个 1 到 9 之间的数字，反斜线匹配之前的用括号括起来的第 digit 个子表达式。 （注意：反斜线在正则表达式中有另一种意义，取决于上下文，它还可能表示 Escape 字符。

表 6：模式交换正则表达式的说明

正则表达式项目	说明
(	第一个子表达式的开头
.	匹配除换行符之外的任意单字符
*	重复操作符，匹配之前的 . 元字符 0 到 n 次
)	第一个子表达式的结尾；匹配结果在 1中获取（在这个例子中，结果为 Ellen。）
	必须存在的空白
(	第二个子表达式的开头
.	匹配除换行符之外的任意单个字符
*	重复操作符，匹配之前的 . 元字符 0 到 n 次
)	第二个子表达式的结尾；匹配结果在 2中获取（在这个例子中，结果为 Hildi。）
	空白
(	第三个子表达式的开头
.	匹配除换行符之外的任意单字符
*	重复操作符，匹配之前的 . 元字符 0 到 n 次
)	第三个子表达式的结尾；匹配结果在 3中获取（在这个例子中，结果为 Smith。）

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/174388/viewspace-913098/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/174388/viewspace-913098/