正则表达式在Oracle10g中的应用--REGEXP_INSTR函数

正则表达式在Oracle10g中的应用--REGEXP_INSTR函数

了解REGEXP_INSTR函数以前，我们先来了解INSTR函数。INSTR返回的是一个字符串在另外一个字符串中的位置，如字符串world在字符串hello world中的位置是7，即下面的语句返回值为7。
SELECT INSTR('hello world','world') FROM DUAL;
REGEXP_INSTR实现的功能与INSTR类似，不同的是REGEXP_INSTR用一个匹配模式代替了具体的字符串，这样就提供了更大的灵活性。下面的这个例子使用REGEXP_INSTR在字符串Joe Smith, 10045 Berry Lane, San Joeseph, CA 91234中返回5个数字组成的邮政编码的起始位置。如果正则表达式写为[[:digit:]]{5}，你将会取得房间号10045而不是邮政编码91234，因为10045也是5个连续的数字。因此，我们要将正则表达式锚在行尾，这要用到元字符$，这样这个函数就会返回邮政编码在字符串中的位置，而不会受到房间号的数字数量的影响了。
SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234','[[:digit:]]{5}$') AS rx_instr
FROM DUAL;
我们现在来扩展这个邮政编码，在前面的例子的基础上增加一个可选的4位数字。其模式为：[[:digit:]]{5}(-[[:digit:]]{4})?$。如果源字符串以5数字，或者以5数字+4数字邮政编码结束，你还是可以看到这个模式在串中的起始位置。
SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234','[[:digit:]]{5}(-[[:digit:]]{4})?$') AS rx_instr
FROM DUAL;
SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234','[[:digit:]]{5}(-[[:digit:]]{4})?$')
AS rx_instr
FROM DUAL;
在这个例子中，在圆扩号内的子表达式(-[[:digit:]]{4})重复0次或1次，这是由重复操作符?来指定的。如果你试图用传统的SQL语句来实现相同的功能，那么即使是SQL专家也会感到困难的。
REGEXP_SUBSTR函数
了解REGEXP_SUBSTR函数以前，我们先来了解SUBSTR函数。SUBSTR函数利用参数指定的位置和字符个数返回一个字符串的子串，如SUBSTR(‘hello world’,1,5)返回子字符串hello。
SELECT SUBSTR('hello world',1,5) FROM DUAL;
REGEXP_SUBSTR函数是利用匹配模式从主字符串中抽取子串。在下面的例子中，函数返回匹配模式“, [^,]*,”的子字符串。正则表达式搜索一个逗号，后接一个空格；然后是0个或多个非逗号的字符（由[^,]*指定），最后是另一个逗号。这个模式类似于查找由逗号分隔的字符串。
SELECT REGEXP_SUBSTR('first field, second field, third field', ', [^,]*,') REGEXP_SUBSTR FROM DUAL;

查询结果为：
REGEXP_SUBSTR
---------------
, second field,
REGEXP_REPLACE函数
了解REGEXP_REPLACE函数以前，我们先来了解REPLACE函数。REPLACE函数根据指定的替代规则将字符串中的子串替换为另一个子串，如REPLACE(‘JACK and JUE’,’J’,’BL’)，其替换结果为’BLACK and BLUE’。
SELECT REPLACE('JACK and JUE','J','BL') FROM DUAL;
REPLACE函数的另外一个用途就是替换多余的空格，如在字符串Joe   Smith中，Joe和Smith之间有3个空格，我们将这三个空格替换为一个。
SELECT REPLACE('Joe   Smith','   ',' ') FROM DUAL;
利用REGEXP_REPLACE函数可以做进一步的替换，下面的查询将两个或者多个空格替换为一个空格，( )子表达式包含一个空格，这个空格可以重复两次或多次，这个重复次数是由{2,}指定的。
SELECT REGEXP_REPLACE('500   Oracle       Parkway,    Redwood   Shores, CA', '( ){2,}',' ') REGEXP_REPLACE
FROM DUAL;

查询结果为：
REGEXP_REPLACE
--------------------------------------
500 Oracle Parkway, Redwood Shores, CA
实验
以上测试在Oracle10g数据库中均可实现，由于所用到的都是DUAL表，因此通过任何用户连接到Oracle都可以。