Oracle 的 REPLACE 和 REGEXP_REPLACE

REPLACE 函数是用另外一个值来替代串中的某个值。例如，可以用一个匹配数字来替代字母的每一次出现。REPLACE 的格式如下所示：

REPLACE ( char, search_string [, replace_string])

如果没有指定replace_string 变量的值，那么当发现search_string 变量的值时，就将其删除。输入可以为任何字符数据类型——CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB或NCLOB。下面是一个示例：

REPLACE('GEORGE', 'GE', 'EG') = EGOREG
REPLACE('GEORGE', 'GE', NULL) = OR

如果搜索串的长度不为零，则可以知道搜索串在某个串中出现的次数。首先，计算源串的长度：

LENGTH('GEORGE')

然后，计算源串删除搜索串以后的长度：

LENGTH(REPLACE('GEORGE', 'GE', NULL))

接着用搜索串的长度除以两次的长度之差，就可以得到搜索串出现的次数：

select LENGTH('GEORGE')
- LENGTH(REPLACE('GEORGE', 'GE', NULL))
/
LENGTH('GE') AS Counter
from DUAL;
COUNTER
-------

REGEXP_REPLACE 函数在几个方面扩展了REPLACE 函数的功能。它支持在搜索模式中使用正则表达式，也支持本章前面描述的变量，即position、occurrence 和match_parameter，从而可以选择只替代某些匹配的值，或者不区分大小写。REGEXP_REPLACE 函数的语法如下所示：

REGEXP_REPLACE( source_string, pattern
[, replace_string
[, position
[, occurrence
[, match_parameter ]
]
]
]
)

除了replace_string，这里所有的变量都已经在本章前面章节作了介绍。replace_string 告诉Oracle 用什么来替代source_string 中与pattern 匹配的部分。occurrence 变量是一个非负整数，它指定操作的次数：如果为0，则所有的匹配项都被替代；如果指定一个正数，则Oracle替代第n 次匹配。

考虑ADDRESS 表中的Phone 列。首先，寻找格式为###-###-#### 的号码。该格式分为3 部分，分别是3 个数字的集合、后面是另3 个数字的一个集合，然后又是4 个数字的一个集合，中间用‘-’符号隔开。通过在REGEXP_SUBSTR 函数调用中查找那些数字集合，可以找到与该标准匹配的行：

select REGEXP_SUBSTR (Phone,
'([[:digit:]]{3})-([[:digit:]]{3})-([[:digit:]]{4})'
) "REGEXP_SUBSTR"
from ADDRESS;
REGEXP_SUBST
------------
213-555-0223
415-555-7530
214-555-8383
312-555-1166
707-555-8900
312-555-1414
415-555-6842
415-555-2178
415-555-7387
415-555-7512
415-555-6252
617-555-0125
603-555-2242
202-555-1414
718-555-1638
214-555-8383
503-555-7491

现在，使用REGEXP_REPLACE 把前3 个数字放在圆括号内，同时省略第一个‘-’符号。为此，我们将第1 个数字集称为 \1，第2 个数据集称为 \2，第3 个数据集称为\3。

select REGEXP_REPLACE (Phone,
'([[:digit:]]{3})-([[:digit:]]{3})-([[:digit:]]{4})'
, '(\1) \2-\3'
) "REGEXP_REPLACE"
from ADDRESS;
REGEXP_REPLACE
------------------------------------------
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]
[phone]

输出说明了REGEXP_REPLACE 函数调用的结果，即区号用圆括号括起来，第一个'-'被去掉。

为了说明occurrenc 变量的工作原理，下面的REGEXP_REPLACE 函数调用是用句点来替代电话号码中的第二个'5'：

 

select REGEXP_REPLACE (Phone,
'5', '.',
1, 2
) "REGEXP_REPLACE"
from ADDRESS;
REGEXP_REPLACE
------------------------------------------
213-5.5-0223
415-.55-7530
214-5.5-8383
312-5.5-1166
707-5.5-8900
312-5.5-1414
415-.55-6842
415-.55-2178
415-.55-7387
415-.55-7512
415-.55-6252
617-5.5-0125
603-5.5-2242
202-5.5-1414
718-5.5-1638
214-5.5-8383
503-.55-7491

可以进一步修改该查询语句，排除前3 个可能匹配的数字(开始位置设为4)，并替代第4次出现：

 

select REGEXP_REPLACE (Phone,
'5', '.',
4, 4
) "REGEXP_REPLACE"
from ADDRESS;
REGEXP_REPLACE
------------------------------------------
213-555-0223
415-555-7.30
214-555-8383
312-555-1166
707-555-8900
312-555-1414
415-555-6842
415-555-2178
415-555-7387
415-555-7.12
415-555-62.2
617-555-012.
603-555-2242
202-555-1414
718-555-1638
214-555-8383
503-555-7491

通过在where 子句中使用REGEXP_INSTR，可以限制返回的行。在本例中，只显示那些至少含有4 个‘5’的行(从第4 个字符开始)。因为该搜索模式并不复杂，所以这里也可以使用INSTR 函数。

select REGEXP_REPLACE (Phone,
'5', '.',
4, 4
) "REGEXP_REPLACE"
from ADDRESS
where REGEXP_INSTR(Phone, '5',4,4) > 0;
415-555-7.30
415-555-7.12
415-555-62.2
617-555-012.

可以使用该功能来搜索可供选择的值，从而在单个查询中组合使用多个查询标准。在下面的示例中，可以替代5 或者2；‘5’和‘2’的出现次数都记入occurrence 统计量中：

select REGEXP_REPLACE (Phone,
'(5|2)', '.',
4, 4
) "REGEXP_REPLACE"
from ADDRESS
where REGEXP_INSTR(Phone, '(5|2)',4,4) > 0;
REGEXP_REPLACE
------------------------------------------
213-555-0.23
415-555-7.30
415-555-684.
415-555-.178
415-555-7.12
415-555-6.52
617-555-01.5
603-555-.242

由于该示例中出现的‘|’符号是一个可选的运算符，因此，匹配两个指定值中任何一个都将返回一行。关于正则表达式中支持的更多运算符，请参阅表8-1。

 

出自：http://book.51cto.com/art/201007/212287.htm