oracleplsql中使⽤正则表达式转
使⽤正规表达式编写更好的 SQL
作者：Alice Rischert
Oracle Database 10g中的正规表达式特性是⼀个⽤于处理⽂本数据的强⼤⼯具
Oracle Database 10g的⼀个新特性⼤⼤提⾼了您搜索和处理字符数据的能⼒。这个特性就是正规表达式，是⼀种⽤来描述⽂本模式的表⽰⽅法。很久以来它已在许多编程语⾔和⼤量 UNIX 实⽤⼯具中出现过了。
Oracle 的正规表达式的实施是以各种 SQL 函数和⼀个WHERE⼦句操作符的形式出现的。如果您不熟悉正规表达式，那么这篇⽂章可以让您了解⼀下这种新的极其强⼤然⽽表⾯上有点神秘的功能。已经对正规表达式很熟悉的读者可以了解如何在 Oracle SQL 语⾔的环境中应⽤这种功能。
后向引⽤
正则表达式的⼀个有⽤的特性是能够存储⼦表达式供以后重⽤；这也被称为后向引⽤（在中对其进⾏了概述）。它允许复杂的替换功能，如在新的位置上交换模式或显⽰重复出现的单词或字母。⼦表达式的
匹配部分保存在临时缓冲区中。缓冲区从左⾄右进⾏编号，并利⽤/digit符号进⾏访问，其中 digit 是 1 到 9 之间的⼀个数字，它匹配第 digit 个⼦表达式，⼦表达式⽤⼀组圆括号来显⽰。
接下来的例⼦显⽰了通过按编号引⽤各个⼦表达式将姓名Ellen Hildi Smith转变为Smith, Ellen Hildi。
SELECT REGEXP_REPLACE(
'Ellen Hildi Smith',
'(.*) (.*) (.*)', '/3, /1 /2')
FROM dual
REGEXP_REPLACE('EL
------------------
regexp likeSmith, Ellen Hildi
该 SQL 语句显⽰了⽤圆括号括住的三个单独的⼦表达式。每⼀个单独的⼦表达式包含⼀个匹配元字符 (.)，并紧跟着*元字符，表⽰任何字符（除换⾏符之外）都必须匹配零次或更多次。空格将各个⼦表达
式分开，空格也必须匹配。圆括号创建获取值的⼦表达式，并且可以
⽤/digit来引⽤。第⼀个⼦表达式被赋值为/1，第⼆个/2，以此类推。这些后向引⽤被⽤在这个函数的最后⼀个参数 (/3, /1 /2) 中，这个函数有效地返回了替换⼦字符串，并按期望的格式来排列它们（包括逗号和空格）。详细说明了该正则表达式的各个组成部分。
后向引⽤对替换、格式化和代替值⾮常有⽤，并且您可以⽤它们来查相邻出现的值。接下来的例⼦显⽰了使⽤REGEP_SUBSTR函数来查任意被空格隔开的重复出现的字母数字值。显⽰的结果给出了识别重复出现的单词 is 的⼦字符串。
SELECT REGEXP_SUBSTR(
'The final test is is the implementation',
'([[:alnum:]]+)([[:space:]]+)/1') AS substr
FROM dual
SUBSTR
------
is is
匹配参数选项
您可能已经注意到了正则表达式操作符和函数包含⼀个可选的匹配参数。这个参数控制是否区分⼤⼩写、换⾏符的匹配和保留多⾏输⼊。
正则表达式的实际应⽤
您不仅可以在队列中使⽤正则表达式，还可以在使⽤ SQL 操作符或函数的任何地⽅（⽐如说在 PL/SQL 语⾔中）使⽤正则表达式。您可以编写利⽤正则表达式功能的触发器，以验证、⽣成或提取值。
接下来的例⼦演⽰了您如何能够在⼀次列检查约束条件中应⽤REGEXP_LIKE操作符来进⾏数据验证。它在插⼊或更新时检验正确的社会保险号码格式。如 123-45-6789 和 123456789 之类格式的社会保险号码对于这种列约束条件是可接受的值。有效的数据必须以三个数字开始，紧跟着⼀个连字符，再加两个数字和⼀个连字符，最后⼜是四个数字。另⼀种表达式只允许 9 个连续的数字。竖线符号 (|) 将各个选项分开。
ALTER TABLE students
ADD CONSTRAINT stud_ssn_ck CHECK
(REGEXP_LIKE(ssn,
'^([[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}|[[:digit:]]{9})$'))
由^和$指⽰的开头或结尾的字符都是不可接受的。确保您的正则表达式没有分成多⾏或包含任何不必要的空格，除⾮您希望格式如此并相应地进⾏匹配。说明了该正则表达式⽰例的各个组成部分。
将正则表达式与现有的功能进⾏⽐较
正则表达式有⼏个优点优于常见的LIKE操作符和INSTR、SUBSTR及REPLACE函数的。这些传统的 SQL 函数不便于进⾏模式匹配。只
有LIKE操作符通过使⽤%和_字符匹配，但LIKE不⽀持表达式的重复、复杂的更替、字符范围、字符列表和 POSIX 字符类等等。此外，新的正则表达式函数允许检测重复出现的单词和模式交换。这⾥的例⼦为您提供了正则表达式领域的⼀个概览，以及您如何能够在您的应⽤程序中使⽤它们。
实实在在地丰富您的⼯具包
因为正则表达式有助于解决复杂的问题，所以它们是⾮常强⼤的。正则表达式的⼀些功能难于⽤传统的 SQL 函数来仿效。当您了解了这种稍显神秘的语⾔的基础构建程序块时，正则表达式将成为您的⼯具包的不可缺少的⼀部分（不仅在 SQL 环境下也在其它的编程语⾔环境下）。为了使您的各个模式正确，虽然尝试和错误有时是必须的，但正则表达式的简洁和强⼤是不容置疑的。
表 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：REGEXP_LIKE操作符
语法说明
REGEXP_LIKE(source_string, pattern
[, match_parameter])source_string⽀持字符数据类型（CHAR、VARCHAR2、CLOB、NCHAR、NVARCHAR2和NCLOB，但不包
括LONG）。pattern参数是正则表达式的另⼀个名称。match_parameter允许可选的参数（如处理换⾏符、保留多⾏格式化以及提供对区分⼤⼩写的控制）。
表 6：REGEXP_INSTR函数
语法说明REGEXP_INSTR(source_string,
pattern
[, start_position
[, occurrence
[, return_option
[, match_parameter]]]])该函数查pattern ，并返回该模式的第⼀个位置。您可以随意指定您想要开始
搜索的start_position。occurrence参数默认为 1，除⾮您指定您要查接下来出现的⼀个模式。return_option的默认值为 0，它返回该
模式的起始位置；值为 1 则返回符合匹配条件的下⼀个字符的起始位置。
表 7： 5 位数字加 4 位表达式的说明
语法说明
必须匹配的空⽩
[:digit:]POSIX 数字类
]字符列表的结尾
{5}字符列表正好重复出现 5 次
(⼦表达式的开头
-⼀个⽂字连字符，因为它不是⼀个字符列表内的范围元字符
[字符列表的开头
[:digit:]POSIX [:digit:]类
[字符列表的开头
]字符列表的结尾
{4}字符列表正好重复出现 4 次
)结束圆括号，结束⼦表达式
??量词匹配分组的⼦表达式 0 或 1 次，从⽽使得 4 位代码可选
$定位元字符，指⽰⾏尾
表 8：REGEXP_SUBSTR函数
语法说明
REGEXP_SUBSTR(source_string, pattern
[, position [, occurrence
[, match_parameter]]])
REGEXP_SUBSTR函数返回匹配模式的⼦字符串。
表 9：REGEXP_REPLACE 函数
语法说明
REGEXP_REPLACE(source_string, pattern
[, replace_string [, position
[,occurrence, [match_parameter]]]])
该函数⽤⼀个指定的replace_string来替换匹配的模式，从⽽允许复杂的“搜索并替换”操作。
表 10：后向引⽤元字符
元字符说明
/digit反斜线紧跟着⼀个 1 到 9 之间的数字，反斜线匹配之前的⽤括号括起来的第 digit 个⼦表达式。
（注意：反斜线在正则表达式中有另⼀种意义，取决于上下⽂，它还可能表⽰ Escape 字符。
表 11：模式交换正则表达式的说明
正则表达式项⽬说明
(第⼀个⼦表达式的开头
.匹配除换⾏符之外的任意单字符
*重复操作符，匹配之前的.元字符 0 到n 次
)第⼀个⼦表达式的结尾；匹配结果在/1中获取（在这个例⼦中，结果为Ellen。）
必须存在的空⽩
(第⼆个⼦表达式的开头
.匹配除换⾏符之外的任意单个字符
*重复操作符，匹配之前的.元字符 0 到n 次
)
第⼆个⼦表达式的结尾；匹配结果在/2中获取（在这个例⼦中，结果为Hildi。）
空⽩
(第三个⼦表达式的开头
.匹配除换⾏符之外的任意单字符
*重复操作符，匹配之前的.元字符 0 到n 次
)第三个⼦表达式的结尾；匹配结果在/3
中获取（在这个例⼦中，结果为Smith。）
表 12：社会保险号码正则表达式的说明
正则表达式项⽬说明
^⾏⾸字符（正则表达式在匹配之前不能有任何前导字符。）
(开始⼦表达式并列出⽤ | 元字符分开的可替换选项
[字符列表的开头
[:digit:]POSIX 数字类
]字符列表的结尾
{3}字符列表正好重复出现 3 次
-连字符
[字符列表的开头
[:digit:]POSIX 数字类
]字符列表的结尾
{2}字符列表正好重复出现 2 次
-另⼀个连字符
[字符列表的开头
[:digit:]POSIX 数字类
]字符列表的结尾
{4}字符列表正好重复出现 4 次
|替换元字符；结束第⼀个选项并开始下⼀个替换表达式[字符列表的开头
[:digit:]POSIX 数字类
]字符列表的结尾
{9}字符列表正好重复出现 9 次
)结束圆括号，结束⽤于替换的⼦表达式组
$定位元字符，指⽰⾏尾；没有额外的字符能够符合模式

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。