主页 > 知识库 > 数据库 > Oracle >

Oracle 正则表达式的问题(2)

来源:中国IT实验室 作者:佚名 发表于:2013-07-18 15:26  点击:
之前的一个例子介绍了使用圆括号来创建一个子表达式;它们允许您通过输入更替元字符来输入可更替的选项,这些元字符由竖线 (|) 分开。 例如,正规表达式t(a|e|i)n允许字母t和n之间的三种可能的字符更替。匹配模式包

之前的一个例子介绍了使用圆括号来创建一个子表达式;它们允许您通过输入更替元字符来输入可更替的选项,这些元字符由竖线 (|) 分开。
    例如,正规表达式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是第一次出现五个连续数字。因此,您必须将表达式定位到该行的末尾,正如$元字符所示,该函数将显示邮政编码的起始位置,而不管门牌号的数字个 数。
    SELECTREGEXP_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 位邮政编码的格式结束,则您将能够显示该模式的起始位置。
    SELECTREGEXP_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函数
    ·?3£àà??óú SUBSTR函数的REGEXP_SUBSTR函数用来提取一个字符串的一部分。表 8显示了这个新函数的语法。在下面的示例中,匹配模式[^,]*的字符串将被返回。该正规表达式搜索其后紧跟着空格的一个逗号;然后按[^,]*的指示搜 索零个或更多个不是逗号的字符,最后查找另一个逗号。这种模式看起来有点像一个用逗号分隔的值字符串。
    SELECTREGEXP_SUBSTR('first field, second field , third field',
    ', [^,]*,')
    FROM dual
    REGEXP_SUBSTR('FIR
    ------------------
    , second field ,
    REGEXP_REPLACE函数
    让我们首先看一下传统的REPLACE SQL 函数,它把一个字符串用另一个字符串来替换。假设您的数据在正文中有不必要的空格,您希望用单个空格来替换它们。利用REPLACE函数,您需要准确地列 出您要替换多少个空格。然而,多余空格的数目在正文的各处可能不是相同的。下面的示例在Joe和Smith之间有三个空格。REPLACE函数的参数指定 要用一个空格来替换两个空格。在这种情况下,结果在原来的字符串的Joe和Smith 之间留下了一个额外的空格。
    SELECTREPLACE('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
    后向引用
    正则表达式的一个有用的特性是能够存储子表达式供以后重用;这也被称为后向引用(在表 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 说明了该正则表达式示例的各个组成部分。
    将正则表达式与现有的功能进行比较
    正则表达式有几个优点优于常见的 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
    [, 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。)
    
    空白
    (
    第三个子表达式的开头 tr>
    .
    匹配除换行符之外的任意单字符
    *
    重复操作符,匹配之前的 . 元字符 0 到 n 次
    )
    第三个子表达式的结尾;匹配结果在 \3
    中获取(在这个例子中,结果为 Smith。)
    表 12:社会保险号码正则表达式的说明
    正则表达式项目
    说明
    ^
    行首字符(正则表达式在匹配之前不能有任何前导字符。)
    (
    开始子表达式并列出用 | 元字符分开的可替换选项
    [
    字符列表的开头
    [:digit:]
    POSIX 数字类
    ]
    字符列表的结尾
    {3}
    字符列表正好重复出现 3 次
    -
    连字符
    [
    字符列表的开头
    [:digit:]
    POSIX 数字类
    ]
    字符列表的结尾
    {2}
    字符列表正好重复出现 2 次
    -
    另一个连字符
    [
    字符列表的开头
    [:digit:]
    POSIX 数字类
    ]
    字符列表的结尾
    {4}
    字符列表正好重复出现 4 次
    |
    替换元字符;结束第一个选项并开始下一个替换表达式
    [
    字符列表的开头
    [:digit:]
    POSIX 数字类
    ]
    字符列表的结尾
    {9}
    字符列表正好重复出现 9 次
    )
    结束圆括号,结束用于替换的子表达式组
    $
    定位元字符,指示行尾;没有额外的字符能够符合模式

有帮助
(0)
0%
没帮助
(0)
0%