使用正规表达式编写更好的 SQL
使用正则表达式编写更好的 SQL （续）
什么是正规表达式？
正规表达式由一个或多个字符型文字和/或元字符组成。在最简单的格式下，正规表达式仅由字符文字组成，如正规表达式 cat。它被读作字母 c，接着是字母 a 和 t，这种模式匹配 cat、location 和 catalog 之类的字符串。元字符提供算法来确定 Oracle 如何处理组成一个正规表达式的字符。当您了解了各种元字符的含义时，您将体会到正规表达式用于查找和替换特定的文本数据是非常强大的。
验证数据、识别重复关键字的出现、检测不必要的空格，或分析字符串只是正规表达式的许多应用中的一部分。您可以用它们来验证电话号码、邮政编码、电子邮件地址、社会安全号码、IP 地址、文件名和路径名等的格式。此外，您可以查找如 HTML 标记、数字、日期之类的模式，或任意文本数据中符合任意模式的任何事物，并用其它的模式来替换它们。
用 Oracle Database 10g 使用正规表达式
您可以使用最新引进的 Oracle SQL REGEXP_LIKE 操作符和 REGEXP_INSTR、REGEXP_SUBSTR 以及 REGEXP_REPLACE 函数来发挥正规表达式的作用。您将体会到这个新的功能如何对 LIKE 操作符和 INSTR、SUBSTR 和 REPLACE 函数进行了补充。实际上，它们类似于已有的操作符，但现在增加了强大的模式匹配功能。被搜索的数据可以是简单的字符串或是存储在数据库字符列中的大量文本。正规表达式让您能够以一种您以前从未想过的方式来搜索、替换和验证数据，并提供高度的灵活性。
正规表达式的基本例子
在使用这个新功能之前，您需要了解一些元字符的含义。句号 (.) 匹配一个正规表达式中的任意字符（除了换行符）。例如，正规表达式 a.b 匹配的字符串中首先包含字母 a，接着是其它任意单个字符（除了换行符），再接着是字母 b。字符串 axb、xaybx 和 abba 都与之匹配，因为在字符串中隐藏了这种模式。如果您想要精确地匹配以 a 开头和以 b 结尾的一条三个字母的字符串，则您必须对正规表达式进行定位。脱字符号 (^) 元字符指示一行的开始，而美元符号 ($) 指示一行的结尾（参见表 1）。因此， 正规表达式 ^a.b$ 匹配字符串 aab、abb 或 axb。将这种方式与 LIKE ²Ù×÷·û提供的类似的模式匹配 a_b 相比较，其中 (_) 是单字符通配符。
默认情况下，一个正规表达式中的一个单独的字符或字符列表只匹配一次。为了指示在一个正规表达式中多次出现的一个字符，您可以使用一个量词，它也被称为重复操作符。.如果您想要得到从字母 a 开始并以字母 b 结束的匹配模式，则您的正规表达式看起来像这样：^a.*b$。* 元字符重复前面的元字符 (.) 指示的匹配零次、一次或更多次。LIKE 操作符的等价的模式是 a%b，其中用百分号 (%) 来指示任意字符出现零次、一次或多次。
表 2 给出了重复操作符的完整列表。注意它包含了特殊的重复选项，它们实现了比现有的 LIKE 通配符更大的灵活性。如果您用圆括号括住一个表达式，这将有效地创建一个可以重复一定次数的子表达式。例如，正规表达式 b(an)*a 匹配 ba、bana、banana、yourbananasplit 等。
Oracle 的正规表达式实施支持 POSIX （可移植操作系统接口）字符类，参见表 3 中列出的内容。这意味着您要查找的字符类型可以非常特别。假设您要编写一条仅查找非字母字符的 LIKE 条件 — 作为结果的 WHERE 子句可能不经意就会变得非常复杂。
POSIX 字符类必须包含在一个由方括号 ([]) 指示的字符列表中。例如，正规表达式 [[:lower:]] 匹配一个小写字母字符，而 [[:lower:]]{5} 匹配五个连续的小写字母字符。
除 POSIX 字符类之外，您可以将单独的字符放在一个字符列表中。例如，正规表达式 ^ab[cd]ef$ 匹配字符串 abcef 和 abdef。必须选择 c 或 d。
除脱字符 (^) 和连字符 (-) 之外，字符列表中的大多数元字符被认为是文字。正规表达式看起来很复杂，这是因为一些元字符具有随上下文环境而定的多重含义。^ 就是这样一种元字符。如果您用它作为一个字符列表的第一个字符，它代表一个字符列表的非。因此，[^[:digit:]] 查找包含了任意非数字字符的模式，而 ^[[:digit:]] 查找以数字开始的匹配模式。连字符 (-) 指示一个范围，正规表达式 [a-m] 匹配字母 a 到字母 m 之间的任意字母。但如果它是一个字符行中的第一个字符（如在 [-afg] 中），则它就代表连字符。
之前的一个例子介绍了使用圆括号来创建一个子表达式；它们允许您通过输入更替元字符来输入可更替的选项，这些元字符由竖线 (|) 分开。
例如，正规表达式 t(a|e|i)n 允许字母 t 和 n 之间的三种可能的字符更替。匹配模式包括如 tan、ten、tin 和 Pakistan 之类的字，但不包括 teen、mountain 或 tune。作为另一种选择，正规表达式 t(a|e|i)n 也可以表示为一个字符列表 t[aei]n。表 4 汇总了这些元字符。虽然存在更多的元字符，但这个简明的概述足够用来理解这篇文章使用的正规表达式。
REGEXP_LIKE 操作符
REGEXP_LIKE 操作符向您介绍在 Oracle 数据库中使用时的正规表达式功能。表 5 列出了 REGEXP_LIKE 的语法。
下面的 SQL 查询的 WHERE 子句显示了 REGEXP_LIKE 操作符，它在 ZIP 列中搜索满足正规表达式 [^[:digit:]] 的模式。它将检索 ZIPCODE 表中的那些 ZIP 列值包含了任意非数字字符的行。
SELECT zip
FROM zipcode
WHERE REGEXP_LIKE(zip, '[^[:digit:]]')
ZIP
-----
ab123
123xy
007ab
abcxy
这个正规表达式的例子仅由元字符组成，更具体来讲是被冒号和方括号分隔的 POSIX 字符类 digit。第二组方括号（如 [^[:digit:]] 中所示）包括了一个字符类列表。如前文所述，需要这样做是因为您只可以将 POSIX 字符类用于构建一个字符列表。
REGEXP_INSTR 函数
这个函数返回一个模式的起始位置，因此它的功能非常类似于 INSTR 函数。新的 REGEXP_INSTR 函数的语法在表 6 中给出。这两个函数之间的主要区别是，REGEXP_INSTR 让您指定一种模式，而不是一个特定的搜索字符串；因而它提供了更多的功能。接下来的示例使用 REGEXP_INSTR 来返回字符串 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 rx_instr
FROM dual
RX_INSTR
----------
45
编写更复杂的模式
让我们在前一个例子的邮政编码模式上展开，以便包含一个可选的四位数字模式。您的模式现在可能看起来像这样：[[:digit:]]{5}(-[[:digit:]]{4})?$。如果您的源字符串以 5 位邮政编码或 5 位 + 4 位邮政编码的格式结束，则您将能够显示该模式的起始位置。
SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234',
' [[:digit:]]{5}(-[[:digit:]]{4})?$')
AS starts_at
FROM dual
STARTS_AT
----------
44
在这个示例中，括弧里的子表达式 (-[[:digit:]]{4}) 将按 ? 重复操作符的指示重复零次或一次。此外，企图用传统的 SQL 函数来实现相同的结果甚至对 SQL 专家也是一个挑战。为了更好地说明这个正规表达式示例的不同组成部分，表 7 包含了一个对单个文字和元字符的描述。
REGEXP_SUBSTR 函数
SUBSTR 函数的 REGEXP_SUBSTR 函数用来提取一个字符串的一部分。表 8 显示了这个新函数的语法。在下面的示例中，匹配模式 [^,]* 的字符串将被返回。该正规表达式搜索其后紧跟着空格的一个逗号；然后按 [^,]* 的指示搜索零个或更多个不是逗号的字符，最后查找另一个逗号。这种模式看起来有点像一个用逗号分隔的值字符串。
SELECT REGEXP_SUBSTR('first field, second field , third field',
', [^,]*,')
FROM dual
REGEXP_SUBSTR('FIR
------------------
, second field ,
REGEXP_REPLACE 函数
让我们首先看一下传统的 REPLACE SQL 函数，它把一个字符串用另一个字符串来替换。假设您的数据在正文中有不必要的空格，您希望用单个空格来替换它们。利用 REPLACE 函数，您需要准确地列出您要替换多少个空格。然而，多余空格的数目在正文的各处可能不是相同的。下面的示例在 Joe 和 Smith 之间有三个空格。REPLACE 函数的参数指定要用一个空格来替换两个空格。在这种情况下，结果在原来的字符串的 Joe 和 Smith 之间留下了一个额外的空格。
SELECT REPLACE('Joe Smith',' ', ' ')
AS replace
FROM dual
REPLACE
---------
Joe Smith
REGEXP_REPLACE 函数把替换功能向前推进了一步，其语法在表 9 中列出。以下查询用单个空格替换了任意两个或更多的空格。( ) 子表达式包含了单个空格，它可以按 {2,} 的指示重复两次或更多次。
SELECT REGEXP_REPLACE('Joe Smith',
'( ){2,}', ' ')
AS RX_REPLACE
FROM dual
RX_REPLACE
----------
Joe Smith
'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。
'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹配 'n' 或 'r'。
'.' 匹配除换行符 n之外的任何单字符。
'?' 匹配前面的子表达式零次或一次。
'*' 匹配前面的子表达式零次或多次。
'+' 匹配前面的子表达式一次或多次。
'( )' 标记一个子表达式的开始和结束位置。
'[]' 标记一个中括号表达式。
'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。
'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。
num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。
create table TEST
(
MC VARCHAR2(60)
);
insert into TEST (MC) values ('b0');
insert into TEST (MC) values ('0b');
insert into TEST (MC) values ('1234-233-3223-2323');
insert into TEST (MC) values ('123-45-5678');
insert into TEST (MC) values ('123-56-1234567890');
insert into TEST (MC) values ('123456789');
insert into TEST (MC) values ('idadfa');
insert into TEST (MC) values ('[a');
insert into TEST (MC) values ('[i');
insert into TEST (MC) values ('[a-c]');
insert into TEST (MC) values ('[a-c]a');
insert into TEST (MC) values ('a[a-c]');
insert into TEST (MC) values ('[bdd-a]');
insert into TEST (MC) values ('[adddddd');
insert into TEST (MC) values ('[eeeea]');
insert into TEST (MC) values ('[eeeee]');
insert into TEST (MC) values ('[b]');
insert into TEST (MC) values ('112233445566778899');
insert into TEST (MC) values ('22113344 5566778899');
insert into TEST (MC) values ('991122334455667788');
insert into TEST (MC) values ('aabbccddee');
insert into TEST (MC) values ('bbaaaccddee');
insert into TEST (MC) values ('eeaabbccdd');
insert into TEST (MC) values ('ab123');
insert into TEST (MC) values ('123xy');
insert into TEST (MC) values ('007ab');
insert into TEST (MC) values ('abcxy');
insert into TEST (MC) values ('The final test is is is how to find duplicate words.');
commit;
select 1,'^[:digit:]',mc from test where regexp_like(mc,'^[:digit:]') --以':digit'中的任何一个字符开头的字符串
union
select 2,'[^[:digit:]]',mc from test where regexp_like(mc,'[^[:digit:]]') --任何含有非数字的字符列表
union
select 3,'^[[:digit:]]',mc from test where regexp_like(mc,'^[[:digit:]]') --数字开头
union
select 4,'^[^[:digit:]]',mc from test where regexp_like(mc,'^[^[:digit:]]') --包含任何非数字开头的
union
select 5,'[[:digit:]]',mc from test where regexp_like(mc,'[[:digit:]]') --任何含有数字的字符列表
create table email
( email varchar2(100)
)
insert into email values('windboy@vip.sina.com');
insert into email values('windboy@sina.com');
insert into email values('window2007@vip.sina.com');
insert into email values('21com@sina.com');
insert into email values('windboy@163.com');
insert into email values('test@mail.vip.sina.com');
insert into email values('test.mail.vip.sina.com');
insert into email values('test');
commit;
select * from email where REGEXP_LIKE(email,'^[[:alnum:]]+@([[:alnum:]]).+');
关于正则表达式的后向引用，暂时还是没法理解
select 0,REGEXP_REPLACE('Ellen Hildi Smith','(.*) (.*) (.*)', '\11\22\33') from dual
union
select 1,REGEXP_REPLACE('Ellen Hildi Smith','(.*)(.*)(.*)', '\11\22\33') from dual --2，3
union
select 2,REGEXP_REPLACE('Ellen Hildi Smith','(.*)(.*) (.*)', '\11\22\33') from dual --2
union
select 3,REGEXP_REPLACE('Ellen Hildi Smith','(.*) (.*)(.*)', '\11\22\33') from dual --3
union
select 4,REGEXP_REPLACE('EllenHildi Smith','(.*) (.*) (.*)', '\11\22\33') from dual
union
select 5,REGEXP_REPLACE('EllenHildi Smith','(.*)(.*)(.*)', '\11\22\33') from dual --2，3
union
select 6,REGEXP_REPLACE('EllenHildi Smith','(.*)(.*) (.*)', '\11\22\33') from dual --2
union
select 7,REGEXP_REPLACE('EllenHildi Smith','(.*) (.*)(.*)', '\11\22\33') from dual --3
union
select 8,REGEXP_REPLACE('Ellen HildiSmith','(.*) (.*) (.*)', '\11\22\33') from dual
union
select 9,REGEXP_REPLACE('Ellen HildiSmith','(.*)(.*)(.*)', '\11\22\33') from dual --2，3
union
select 10,REGEXP_REPLACE('Ellen HildiSmith','(.*)(.*) (.*)', '\11\22\33') from dual --2
union
select 11,REGEXP_REPLACE('Ellen HildiSmith','(.*) (.*)(.*)', '\11\22\33') from dual --3
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6517/viewspace-611082/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/6517/viewspace-611082/
803
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
