oracle在10g之前，对字符的处理，主要有like,instr,replace,substr，但由于不支持正则表达式，对某些文字处理，很是费神，且因正则表达式表达式应用甚广，oracle也丢不起这个面子,终于在10g版本中增加了对正则表达式的支持，共有四个函数,regexp_like,regexp_instr,regexp_replace,regexp_substr，本人在互联网点击流数据仓库的开发中，很多时候需要对特定字符，按一定的规则(正则)做转义替换等操作,很多时用了正则表达式，省事很多。下面的文章转自OTN
使用正规表达式编写更好的SQL
作者：Alice Rischert
http://www.oracle.com/technology/global/cn/oramag/webcolumns/2003/techarticles/rischert_regexp_pt1.html
Oracle Database 10g中的正规表达式特性是一个用于处理文本数据的强大工具
Oracle Database 10g的一个新特性大大提高了您搜索和处理字符数据的能力。这个特性就是正规表达式，是一种用来描述文本模式的表示方法。很久以来它已在许多编程语言和大量UNIX实用工具中出现过了。
Oracle的正规表达式的实施是以各种SQL函数和一个WHERE子句操作符的形式出现的。如果您不熟悉正规表达式，那么这篇文章可以让您了解一下这种新的极其强大然而表面上有点神秘的功能。已经对正规表达式很熟悉的读者可以了解如何在Oracle SQL语言的环境中应用这种功能。
什么是正规表达式？
正规表达式由一个或多个字符型文字和/或元字符组成。在最简单的格式下，正规表达式仅由字符文字组成，如正规表达式cat。它被读作字母c，接着是字母a和t，这种模式匹配cat、location和catalog之类的字符串。元字符提供算法来确定Oracle如何处理组成一个正规表达式的字符。当您了解了各种元字符的含义时，您将体会到正规表达式用于查找和替换特定的文本数据是非常强大的。
验证数据、识别重复关键字的出现、检测不必要的空格，或分析字符串只是正规表达式的许多应用中的一部分。您可以用它们来验证电话号码、邮政编码、电子邮件地址、社会安全号码、IP地址、文件名和路径名等的格式。此外，您可以查找如HTML标记、数字、日期之类的模式，或任意文本数据中符合任意模式的任何事物，并用其它的模式来替换它们。
用Oracle Database 10g使用正规表达式
您可以使用最新引进的Oracle SQLREGEXP_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函数
让我们首先看一下传统的REPLACESQL函数，它把一个字符串用另一个字符串来替换。假设您的数据在正文中有不必要的空格，您希望用单个空格来替换它们。利用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
Oracle Database 10g内幕
使用正则表达式编写更好的SQL（续）
作者：Alice Rischert
正则表达式的一个有用的特性是能够存储子表达式供以后重用；这也被称为后向引用（在表10中对其进行了概述）。它允许复杂的替换功能，如在新的位置上交换模式或显示重复出现的单词或字母。子表达式的匹配部分保存在临时缓冲区中。缓冲区从左至右进行编号，并利用\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
------------------
Smith, Ellen Hildi
该SQL语句显示了用圆括号括住的三个单独的子表达式。每一个单独的子表达式包含一个匹配元字符(.)，并紧跟着*元字符，表示任何字符（除换行符之外）都必须匹配零次或更多次。空格将各个子表达式分开，空格也必须匹配。圆括号创建获取值的子表达式，并且可以用\digit来引用。第一个子表达式被赋值为\1，第二个\2，以此类推。这些后向引用被用在这个函数的最后一个参数(\3, \1 \2)中，这个函数有效地返回了替换子字符串，并按期望的格式来排列它们（包括逗号和空格）。表11详细说明了该正则表达式的各个组成部分。
后向引用对替换、格式化和代替值非常有用，并且您可以用它们来查找相邻出现的值。接下来的例子显示了使用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})$'))
由^和$指示的开头或结尾的字符都是不可接受的。确保您的正则表达式没有分成多行或包含任何不必要的空格，除非您希望格式如此并相应地进行匹配。表12说明了该正则表达式示例的各个组成部分。
| 接下来的步骤 访问Oracle Database 10g页面： |
将正则表达式与现有的功能进行比较
正则表达式有几个优点优于常见的LIKE操作符和INSTR、SUBSTR及REPLACE函数的。这些传统的SQL函数不便于进行模式匹配。只有LIKE操作符通过使用%和_字符匹配，但LIKE不支持表达式的重复、复杂的更替、字符范围、字符列表和POSIX字符类等等。此外，新的正则表达式函数允许检测重复出现的单词和模式交换。这里的例子为您提供了正则表达式领域的一个概览，以及您如何能够在您的应用程序中使用它们。
实实在在地丰富您的工具包
因为正则表达式有助于解决复杂的问题，所以它们是非常强大的。正则表达式的一些功能难于用传统的SQL函数来仿效。当您了解了这种稍显神秘的语言的基础构建程序块时，正则表达式将成为您的工具包的不可缺少的一部分（不仅在SQL环境下也在其它的编程语言环境下）。为了使您的各个模式正确，虽然尝试和错误有时是必须的，但正则表达式的简洁和强大是不容置疑的。
Alice Rischert(ar280@yahoo.com)是哥伦比亚大学计算机技术与应用系的数据库应用程序开发和设计方向的主席。她编写了Oracle SQL交互手册第2版（Prentice Hall，2002）和即将推出的Oracle SQL示例（Prentice Hall，2003）。Rischert拥有超过15年的经验在财富100强公司内担任数据库设计师、DBA和项目主管，并且她自从Oracle version 5起就一直使用Oracle产品。
表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 | source_string支持字符数据类型（CHAR、VARCHAR2、CLOB、NCHAR、NVARCHAR2和NCLOB，但不包括LONG）。pattern参数是正则表达式的另一个名称。match_parameter允许可选的参数（如处理换行符、保留多行格式化以及提供对区分大小写的控制）。 'c', which specifies case sensitive matching (default). |
表6：REGEXP_INSTR函数
| 语法 | 说明 |
| REGEXP_INSTR(source_string, pattern | 该函数查找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 | REGEXP_SUBSTR函数返回匹配模式的子字符串。 |
表9：REGEXP_REPLACE函数
| 语法 | 说明 |
| REGEXP_REPLACE(source_string, pattern | 该函数用一个指定的replace_string来替换匹配的模式，从而允许复杂的“搜索并替换”操作。 |
表10：后向引用元字符
| 元字符 | 说明 | |
| \digit | 反斜线 | 紧跟着一个1到9之间的数字，反斜线匹配之前的用括号括起来的第digit个子表达式。 |
表11：模式交换正则表达式的说明
| 正则表达式项目 | 说明 |
| ( | 第一个子表达式的开头 |
| . | 匹配除换行符之外的任意单字符 |
| * | 重复操作符，匹配之前的.元字符0到n次 |
| ) | 第一个子表达式的结尾；匹配结果在\1 |
|
| 必须存在的空白 |
| ( | 第二个子表达式的开头 |
| . | 匹配除换行符之外的任意单个字符 |
| * | 重复操作符，匹配之前的.元字符0到n次 |
| ) | 第二个子表达式的结尾；匹配结果在\2 |
|
| 空白 |
| ( | 第三个子表达式的开头 |
| . | 匹配除换行符之外的任意单字符 |
| * | 重复操作符，匹配之前的.元字符0到n次 |
| ) | 第三个子表达式的结尾；匹配结果在\3 |
表12：社会保险号码正则表达式的说明
| 正则表达式项目 | 说明 |
| ^ | 行首字符（正则表达式在匹配之前不能有任何前导字符。） |
| ( | 开始子表达式并列出用|元字符分开的可替换选项 |
| [ | 字符列表的开头 |
| [:digit:] | POSIX数字类 |
| ] | 字符列表的结尾 |
| {3} | 字符列表正好重复出现3次 |
| - | 连字符 |
| [ | 字符列表的开头 |
| [:digit:] | POSIX数字类 |
| ] | 字符列表的结尾 |
| {2} | 字符列表正好重复出现2次 |
| - | 另一个连字符 |
| [ | 字符列表的开头 |
| [:digit:] | POSIX数字类 |
| ] | 字符列表的结尾 |
| {4} | 字符列表正好重复出现4次 |
| | | 替换元字符；结束第一个选项并开始下一个替换表达式 |
| [ | 字符列表的开头 |
| [:digit:] | POSIX数字类 |
| ] | 字符列表的结尾 |
| {9} | 字符列表正好重复出现9次 |
| ) | 结束圆括号，结束用于替换的子表达式组 |
| $ | 定位元字符，指示行尾；没有额外的字符能够符合模式 |
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20957014/viewspace-590880/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/20957014/viewspace-590880/
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
