【转】正则表达式——Oracle篇

发表人:jordan1226 | 发表时间: 2007年十月30日, 14:33

Oracle 8 和Oracle 9i中缺乏灵活性的SQL 正则表达式最终在Oracle 10g中得到了解决。Oracle 数据库目前内建了符合POSIX 标准的正则表达式。
四个新的函数分别是：REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和EGEXP_REPLACE。它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法，但是它们使用POSIX 正则表达式代替了老的百分号（%）和通配符（_）字符。

POSIX 正则表达式由标准的元字符（metacharacters）所构成：

'^' 表示字符串的开始
'$' 表示字符串的结束
'.' 表示任何字符
字符的范围，比如说'[a-z]'，表示任何ASCII 小写字母，与字符类"[[:lower:]]"" 等价
'?' 允许一个后继字符匹配零次或一次
'+' 允许一个后继字符匹配一次或多次
'*' 表示零次或多次
可以使用"{m,n}" 指定一个精确地出现范围，其意思是"出现从m 次到n 次"；"{m}" 表示"正好m次"；而"{m,}" 表示"至少m次"。还可以使用圆括号组合字符的集合，使用"|"（竖线）表示可替换。例如，字符串'^([a-z]+|[0-9]+)$'将匹配所有由小写字母或数字组合成的字符串。

REGEXP_LIKE 与LIKE 操作符相似。如果第一个参数匹配正则表达式它就解析为TRUE。例如Where REGEXP_LIKE(ENAME,'^J[AO]','i') 将在ENAME 以JA 或JO 开始的情况下返回一行数据。'I' 参数指定正则表达式是大小写敏感的。另外还可以在CHECK 约束和函数索引中指定REGEXP_LIKE。例如：

Alter TABLE EMP ADD CONSTRAINT REGEX01
CHECK (REGEXP_LIKE(ENAME,'^[[:alpha:]]+$'));

这条语句使得ENAME 字段只能包含字母和数字字符（也就是说没有空格或者标点符号）。试图插入或者更新这些数据将导致一个ORA-2290 异常，或者检查约束的有效性。

REGEXP_INSTR 与INSTR 函数类似。它返回一个字符串中匹配一个正则表达式的第一个子串的开始位置。例如：

Select REGEXP_INSTR('The total is $400 for your purchase.','$[[:digit:]]+')
FROM DUAL;

这个查询返回14，即$400在字符串的开始位置。另外还可以指定子串出现的次数；开始搜索的位置；是返回匹配的位置还是返回匹配之后字符的位置。

REGEXP_SUBSTR 返回匹配一个正则表达式的子串。虽然结合使用SUBSTR 和REGEXP_INSTR 及LENGTH 也可以实现这一功能，但是使用这个函数却更为简单。

Select REGEXP_INSTR('one,two,three','[^,]*') FROM DUAL;

这个查询返回'one'，将第一个参数看成一个逗号分隔的列表并返回第一个逗号之前的所有字符。

REGEXP_REPLACE 返回初始参数被匹配子串替换之后的结果。例如：

Select REGEXP_REPLACE('The temperature is 23°F',
'([[:digit:]])+°F',
('1'-32)*5/9||'°C')
FROM DUAL;

这个查询将查找一个华氏温度并将其转换为摄氏度。它将返回：'The temperature is -5°C'。

下面是其官方文档：
Writing Better SQL Using Regular Expressions

By Alice Rischert

10g 的正则表达式是处理文本数据的一个强有力的工具, 正则表达式在许多程序
语言和Unix下已经使用很久了.
oracle 通过在SQL函数或者Where子句中执行正则表达式, 如果你对正则表达式不熟悉，
这篇文章可以给你一个大概的介绍, 熟悉正则表达式的读者可以了解怎么在Oracle SQL语言中实现这个功能
什么是正则表达式？
正则表达式由一个或多个字符和/或者字符元， 最简单的形式由字符组成， 像正则表达式cat,
被解释为字符c后面跟着a和t, 这样的模式匹配字符串有 cat, location 和catalog. 字符元为Oracle怎么处理

正则表达式提供了运算规则。 如果, 你了解了各种字符元的意思, 你就明白正则表达式在处理独立和代替文本

字符方面是多么的强大.
数据有效性， 重复词的辨认, 无关的空白检测，或者分解多个正则组成的字符串， 可以用来检查电话号码

的有效性， 邮政编码，E-mail地址， 社保号码，IP地址， 文件名和路径， 等等。甚至可以用来模式定位,

如HTML的标签，数字， 日期，或者任何模式匹配的字符或者代替他们的模式
在Oracle 10g中使用正则表达式

为了控制正则表达式可以使用Oracle中新的REGEXP_LIKE, REGEXP_INSTR, 和REG_EXP_REPLACE 函数， 你将会

看到这些函数怎样增强了LIKE, INSTR, SUBSTR和REPLACE函数的功能. 实际上，和已经存在的函数呵操作相似

，只是提供了强大的字符匹配能力。
正则表达式简单的例子
使用新功能之前， 要了解一些字符元的含义。句号(.)匹配正则表达式中的任一个字符(不包括新的一行). 例

如正则表达式a.b匹配一个包括a， 然后任意一个字符， 然后是b的字符串， 如字符串axb, xaybx, abba。 如

果要匹配以a开头以b结尾的三个字符的字符串, 必须使用^字符元指定开始行, 以$符号结束， 因此, 正规表达

式^a.b$匹配的字符串如: aab, abb 或者axb, 就像LIKE 操作一样 a_b一样。
默认的, 单个字符或者字符串列表再正则表达式中只匹配一次，如过要多次匹配，必须使用循环操作。 如果

你想匹配一个以a开头， 以b结尾的字符串， 正则表达式为：^a.*b$, *字符元重复.0次，1次或者多次。
表2列出了完全的重复操作。 如果在表达式重使用圆括号, 可以创建子表达式，被重复执行数次。 例如, 正

则表达式b(an)*a 可以匹配 ba, bana, banana, yourbananasplit, 等等
oracle正则表达式支持POSIX字符类，见表3。 那意味着你可以很精确的使用你想要的字符类型。 想象一下

为一个不是按照字母顺序的纪录写一个LIKE条件的查询， Where条件很容易变得非常复杂
POSIX字符类必须以方括号结束([]), 例如正则表达式[[]]匹配一个小写字符, [[]]{5}匹配5

个连续的小写字符
除了POSIX字符类以外， 还可以在字符列中放置单独的字符， 例如正则表达式^ab[cd]ef$匹配字符串abcef

和abdef, 字符c或者d必须被选中.
大部分字符串列表中的字符元从字面上解释, 除了字符"^"和"-". 正则表达式看起来很复杂是由于有些字符元

有多种意思， 这取决于上下文, "^"就是这样的一个字符元. 如果"^"是字符列中的第一个字符，就表示对这个

字符串取反，因此, [^[]]就是表示查找不包含数字的模式, ^[[]]匹配以数字开头的字符串.

字符"-"表示一个范围，正则表达式[a-m]匹配从a到m的字母, 但是如果字符"-"在开头，就表示一个连字符，

如[-afg].
前面的一个例子介绍了用圆括号创建子表达式, 可以用竖线(|)进入循环交替. 例如, 正则表达式t(a|e|i)n

允许t与n中的三个字符可以任选一个, 匹配的字符串例如tan, tin, 和Pakistan, 但不包括teen, mountain或

者tune. 作为可选择的,
表达式t(a|e|i)n 也可以被理解为字符列t[aei]n. 表4列出了这些字符元。
REGEXP_LIKE 操作
下面的SQL查询中Where从句显示了怎么使用REGEXP_LIKE函数， 在ZIP列中查询满足正则表达式

[^[]]的模式。 查询返回ZIPCODE表中包含字符不全是数字的行:
Select zip
FROM zipcode
Where REGEXP_LIKE(zip, '[^[]]')
ZIP
-----
ab123
123xy
007ab
abcxy

这个例子中正则表达式只是由字符元组成, 更多明确的数字字符类用冒号和方括号标示. 第二组括号嵌套了一

个字符类列表，就像前面说的，这是个必不可少的， 你可以用POSIX字符类来创建字符列表
REGEXP_INSTR函数
这个函数返回模式的开始位置， 有点像INSTR函数, 语法见表6，两个函数不同之处是REGECP_INSTR可以让你指

定一个模式而不是特定的查找字符串，这样就提供了强大的功能, 下面的例子使用REGEXP_INSTR返回字符串

"Joe Smith, 10045 Berry Lane, San Joseph, CA 91234"中5个邮政编码的开始位置，如果正则表达式写成

[[]]{5}, 得到的将是门牌号而不是邮编, 因为10045是第一个以5个数字出现的模式， 因此必须用字符

元"$"表明是最后一个, 这个函数将返回邮编的开始位置而不管门牌号：
Select REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234',
'[[]]{5}$')
AS rx_instr
FROM dual
RX_INSTR
----------
45

写一些复杂的模式
我们扩展一下前面邮政编码的模式匹配， 包括一个可选的4个数字，正则表达式为:

[[]]{5}(-[[]]{4})?$. 如果你的源字符串以5个数字的邮编代码或者5个数字的邮编+4个数字的

邮编组成, 这个正则表达式可以用来找到开始位置
Select REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234',
' [[]]{5}(-[[]]{4})?$')
AS starts_at
FROM dual
STARTS_AT
---------- 44
这个例子中括号内的表达式(-[[]]{4}) 被重复0次或者1次， 由"?"循环操作控制。如果试图用传统的

SQL函数取得这样的结果对SQL专家来说都是很困难的。 要想对正则表达式的各种变量有个了解， 参见表7.
REGEXP_SUBSTR 函数
和SUBSTR函数差不多, 分解出字符串的一部分, 表8列出了这个函数的用法。 下面的例子中，返回匹配模式",

[^,]*,"的字符串，正则表达式查找逗号后面跟一个空格，后面跟着非","的字符或者字符串, 最后以逗号结束

。
Select REGEXP_SUBSTR('first field, second field , third field',
', [^,]*,')
FROM dual
REGEXP_SUBSTR('FIR
------------------
, second field ,

REGEXP_REPLACE 函数
传统的REPLACE函数, 用一个字符串代替另一个， 假设你的数据里面有很多的空白字符要用一个空白字符替换

， 用REPLACE函数的话， 必须指出替换多少个空白字符。 下面的例子中在Joe和Smith中间有3个空白,

REPLACE函数的参数指定用一个空白字符替换两个空白字符, 这样就把原来的一个没有替换
Select REPLACE('Joe Smith',' ', ' ')
AS replace
FROM dual
REPLACE
---------
Joe Smith
REGEXP_RELPLACE 函数会好一些， 语法在表9中列出。 下面的例子用一个空格替换两个或者多个空格。 ()子

表达式包含一个空格， 可以重复一次或者多次， 由{2,}指示.

Select REGEXP_REPLACE('Joe Smith',
'( ){2,}', ' ')
AS RX_REPLACE
FROM dual
RX_REPLACE
----------
Joe Smith

Backreference

正则表达式的一个很有用的特点是可以保存子表达式以后使用， 被称为Backreferencing(在表10中汇总). 允

许复杂的替换能力如调整一个模式到新的位置或者指示被代替的字符或者单词的位置. 被匹配的子表达式存储

在临时缓冲区中， 缓冲区从左到右编号, 通过数字符号访问。
下面的例子列出了把名字 ELLen Hildi Smith 变成Smith, Ellen Hildi.
Select REGEXP_REPLACE(
'Ellen Hildi Smith',
'(.*) (.*) (.*)', '3, 1 2')
FROM dual
REGEXP_REPLACE('EL
------------------
Smith, Ellen Hildi

这个SQL语句显示了3个独立的以圆括号表示的子表达式, 每个子表达式匹配任意一个字符元, 圆括号创建的子

表达式获得的值以数字参考， 第一个子表达式被赋予1, 第二个被赋予2...等等. Backreference在最后一

个参数中使用， 有效的返回了替换字符串的格式(包括逗号和空格). 表11列出了这种正则表达式的各个组成部

分
Backreference 替换， 格式化， 取值方面很有用的. 下面的例子用REGEXP_SUBSTR函数来查找重复出现的以

空格分开的词。 显示的结果指出重复的子串。

Select REGEXP_SUBSTR(
'The final test is is the implementation',
'([[]]+)([[]]+)1') AS substr
FROM dual
SUBSTR
------
is is

匹配参数选项

你可能已经注意到了正则表达式操作和函数包含了一个可选的匹配参数. 这个参数可以控制大小写, 匹配新一

行的字符， 合并多行输入。

正则表达式的实践应用
正则表达式不仅可以用在查询中， 还可以用在任何可以使用SQL操作或者函数的地方，例如PL/SQL语言中. 可

以在触发器中利用正则表达式的功能来检查数据有效性， 产生， 或者提取数值。
下面的例子演示了怎么用REGEXP_LIKE 操作再列中检查数据约束的有效性. 在insert或者update的时候检查社

保号码的正确格式. 社保号的格式像123-45-6789, 123456789, 有效的数字必须以3个数字开头, 跟着一个连字

符, 两个数字， 一个连字符， 最后是4个数字。 交替出现的表达式只能出现9个数字， 竖线分开每个单独的

选择.
Alter TABLE students
ADD CONSTRAINT stud_ssn_ck CHECK
(REGEXP_LIKE(ssn,
'^([[]]{3}-[[]]{2}-[[]]{4}|[[]]{9})$'))
以字符开始或者结尾都是不允许的，这个由"^"和"$."可以看出。 必须注意正则表达式不能被分开成多行或者

有空格. 表12解释了正则表达式单独组成部分的例子。
参见10g的主页/products/database/oracle10g/index.html

正则表达式和其他已经存在的函数的比较

正则表达式比一般的LIKE, INSTR, SUBSTR和REPLACE函数有很多好处， 传统的SQL函数没有方便的模式匹配功

能， 虽然通过使用"%"和"_", LIKE操作可以匹配字符串， 但LIKE不能支持重复的表达式, 复杂的交替, 字符

范围， 字符列, POSIX字符类等等. 此外， 新的正则表达式可以检测重复词和交换匹配模式的位置， 上面的

例子可以大概看出怎么在应用中使用正则表达式


非常稳固的与你的工具包结合


正则表达式是很有用的， 可以帮助你解决复杂的问题。 一些正则表达式的功能用传统的SQL函数很难表达出来

。 当你学会了这个秘密语言之后， 正则表达式将会成为你的工具包里不可缺少的一部分， 不仅仅是SQL， 还

包括其他的程序语言，虽然有时候要经过反复试验你的模式才能正确， 正则式的功能是显而易见的.