快捷搜索:

使用正规表达式编写更好的SQL

Oracle Database 10g 的一个新特点大年夜大年夜前进了您搜索和处置惩罚字符数据的能力。这个特点便是正规表达式,是一种用来描述文本模式的表示措施。好久以来它已在许多编程说话和大年夜量 UNIX 实用对象中呈现过了。

Oracle 的正规表达式的实施因此各类 SQL 函数和一个 WHERE 子句操作符的形式呈现的。假如您不认识正规表达式,那么这篇文章可以让您懂得一下这种新的极其强大年夜然而外面上有点神秘的功能。已经对正规表达式很认识的读者可以懂得若何在 Oracle 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

您可能还会对下面的文章感兴趣: