Oracle 的 REPLACE 和 REGEXP_REPLACE

7612z408ro

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  
  
• ------------------------------------------  
  
• (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 函数调用的结果，即区号用圆括号括起来，第一个'-'被去掉。
为了说明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.iyunv.com/art/201007/212287.htm