Oracle字符串替换：一次搞定

一、替换函数

Oracle字符串替换是一个常见的需求，Oracle提供了一个替换函数REPLACE()来实现。REPLACE()函数的用法如下:

REPLACE(string1, string_to_replace [, replace_with_string])

其中string1表示要替换的字符串，string_to_replace表示要被替换的字符串，replace_with_string表示替换后的字符串，可以省略。如果省略replace_with_string，则使用空字符串来替换。例如：

SELECT REPLACE('hello world', 'world') FROM dual;

将输出"hello"

如果替换的字符串在string1中出现了多次，那么它们都会被替换。例如：

SELECT REPLACE('aaa', 'a') FROM dual;

将输出" "

二、多个替换字符串

REPLACE()函数只能替换一个字符串。如果需要替换多个字符串，可以结合使用多个REPLACE()函数。例如：

SELECT REPLACE(REPLACE('hello world', 'hello'), 'world') FROM dual;

将输出""

可以看出，先将"hello"替换为""，再将"world"替换为""，最后得到的结果是""。

如果需要替换的字符串比较多，使用多个REPLACE()函数会比较繁琐。这时可以使用REGEXP_REPLACE()函数，结合正则表达式实现。例如：

SELECT REGEXP_REPLACE('hello world', '(hello|world)', '') FROM dual;

将输出""

三、模式匹配

REGEXP_REPLACE()函数支持正则表达式，可以实现更加灵活的字符串替换。例如，可以用正则表达式来匹配多种模式的字符串，然后进行替换。例如：

SELECT REGEXP_REPLACE('hello 123 world', '\d+', '') FROM dual;

将输出"hello world"。在正则表达式"\d+"中，\d表示数字，+表示匹配一个或多个数字。所以，这个正则表达式可以匹配1个或多个数字。

在正则表达式中，还可以使用一些特殊字符，如"*"表示匹配0个或多个字符，"."表示匹配除换行符外的任意1个字符。例如：

SELECT REGEXP_REPLACE('hello world', '<.*>', '') FROM dual;

将输出"hello world"。在正则表达式"<.*>"中，"."表示匹配除换行符外的任意1个字符，"*"表示匹配0个或多个字符。所以，这个正则表达式可以匹配任意的HTML标签，将它们替换为空字符串。

四、案例展示

下面是一个实际的案例，我们要将一个字符串中的所有URL替换为链接文本，并且去掉URL中的参数部分。例如：

SELECT replace_urls('这是一个网页，访问地址是http://www.example.com/index.php?param=value，欢迎访问。')
FROM dual;

期望输出结果是："这是一个网页，访问地址是http://www.example.com/，欢迎访问。"

实现这个功能，首先要匹配所有的URL，可以使用正则表达式："http://[^\s]+"。其中[^\s]+表示匹配除空格外的任意1个或多个字符。

然后，对于每个匹配到的URL，需要将其替换为HTML格式的链接文本。可以使用REPLACE()函数来替换。例如：

REPLACE('访问地址是http://www.example.com/index.php?param=value，欢迎访问。', 
'http://www.example.com/index.php?param=value', 
'http://www.example.com/')

将输出："访问地址是http://www.example.com/，欢迎访问。"

最后，利用这两个步骤，就可以实现字符串中所有URL的替换。具体的实现代码如下：

CREATE OR REPLACE FUNCTION replace_urls(p_str IN VARCHAR2) RETURN VARCHAR2 IS 
  l_pattern VARCHAR2(100) := 'http://[^\s]+'; 
  l_html_tpl VARCHAR2(100) := '&1'; 
  l_url VARCHAR2(2000); 
  l_new_str VARCHAR2(2000) := p_str; 
BEGIN 
  FOR i IN 1..REGEXP_COUNT(p_str, l_pattern) LOOP 
    l_url := REGEXP_SUBSTR(p_str, l_pattern, 1, i); 
    l_new_str := REPLACE(l_new_str, l_url, REPLACE(l_html_tpl, '&1', l_url)); 
  END LOOP; 
  RETURN l_new_str; 
END;