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;