Oracle 儲存程序去除重複字串函數

函數用途

移除以指定分隔符分隔的字串中重複項目。例如 MyReplace('13,14,13,444', ',') 返回 '13,14,444'。

完整函數代碼

CREATE OR REPLACE FUNCTION MyReplace(
  oldStr IN VARCHAR2,
  sign   IN VARCHAR2
) RETURN VARCHAR2 IS
  str           VARCHAR2(1000) := oldStr;
  currentIndex  NUMBER := 0;
  startIndex    NUMBER := 0;
  endIndex      NUMBER;
  TYPE str_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
  arr           str_type;
  Result        VARCHAR2(1000);
BEGIN
  IF oldStr IS NULL THEN
    RETURN '';
  END IF;

  -- 1. 解析字串填入關聯陣列
  LOOP
    currentIndex := currentIndex + 1;
    endIndex := INSTR(str, sign, 1, currentIndex);
    IF endIndex <= 0 THEN
      EXIT;
    END IF;
    arr(currentIndex) := TRIM(SUBSTR(str, startIndex + 1, endIndex - startIndex - 1));
    startIndex := endIndex;
  END LOOP;

  -- 取最後一段
  arr(currentIndex) := SUBSTR(str, startIndex + 1);

  -- 2. 雙層迴圈標記重複項為空
  FOR i IN 1 .. currentIndex - 1 LOOP
    FOR j IN i + 1 .. currentIndex LOOP
      IF arr(i) = arr(j) THEN
        arr(j) := '';
      END IF;
    END LOOP;
  END LOOP;

  -- 3. 重組字串
  str := '';
  FOR i IN 1 .. currentIndex LOOP
    IF arr(i) IS NOT NULL THEN
      str := str || sign || arr(i);
    END IF;
    arr(i) := ''; -- 清理陣列
  END LOOP;

  -- 4. 移除前導分隔符
  Result := SUBSTR(str, 2);
  RETURN Result;
END MyReplace;
/

使用範例

SELECT MyReplace('apple,banana,apple,orange,banana', ',') FROM dual;
-- 返回 'apple,banana,orange'

SELECT MyReplace('1|2|3|2|1', '|') FROM dual;
-- 返回 '1|2|3'

效能考量與改進建議

• 目前為 O(n²) 雙層迴圈，適合資料量較小（<1000 項）場景。
• 大量資料建議改用 COLLECTION + SET 操作，或 Oracle 10g+ 的 MULTISET EXCEPT。
• 可增加 DETERMINISTIC 關鍵字利用函數結果快取。

現代化寫法參考

CREATE OR REPLACE FUNCTION RemoveDuplicates(
  p_str  IN VARCHAR2,
  p_sep  IN VARCHAR2 DEFAULT ','
) RETURN VARCHAR2 DETERMINISTIC IS
  v_arr   SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  v_res   VARCHAR2(4000);
BEGIN
  -- 使用 REGEXP_SUBSTR 分割
  FOR i IN 1 .. REGEXP_COUNT(p_str, '[^'||p_sep||']+') LOOP
    v_arr.EXTEND;
    v_arr(i) := TRIM(REGEXP_SUBSTR(p_str, '[^'||p_sep||']+', 1, i));
  END LOOP;
  
  -- 排序去重
  SELECT LISTAGG(column_value, p_sep) WITHIN GROUP (ORDER BY column_value)
  INTO v_res
  FROM TABLE(CAST(MULTISET(SELECT DISTINCT column_value FROM TABLE(v_arr)) AS SYS.ODCIVARCHAR2LIST));
  
  RETURN v_res;
END;
/