Oracle PL/SQL 實用函數：去除字串中重複片段（指定分隔符），支援泛型呼叫

在 ETL、資料清洗、報表參數解析等場景，常需從「逗號分隔字串」中去除重複值。本文提供一個通用 PL/SQL 函數 my_replace，支援任意分隔符、自動修剪空白、保留首次出現順序，並附效能優化版與測試案例。

一、基礎版實作（關聯陣列 + 雙迴圈去重）

CREATE OR REPLACE FUNCTION my_replace(
  p_str  IN VARCHAR2,
  p_sep  IN VARCHAR2 DEFAULT ','
) RETURN VARCHAR2 IS
  v_str       VARCHAR2(32767) := p_str;
  v_idx       PLS_INTEGER := 0;
  v_start     PLS_INTEGER := 0;
  v_end       PLS_INTEGER;
  TYPE arr_t IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;
  v_arr       arr_t;
  v_result    VARCHAR2(32767);
BEGIN
  IF v_str IS NULL THEN RETURN NULL; END IF;

  -- 1. 切分字串填入陣列
  LOOP
    v_idx := v_idx + 1;
    v_end := INSTR(v_str, p_sep, 1, v_idx);
    IF v_end = 0 THEN EXIT; END IF;
    v_arr(v_idx) := TRIM(SUBSTR(v_str, v_start + 1, v_end - v_start - 1));
    v_start := v_end;
  END LOOP;
  -- 最後一段
  v_arr(v_idx) := TRIM(SUBSTR(v_str, v_start + 1));

  -- 2. 雙迴圈去重（保留首次出現）
  FOR i IN 1 .. v_idx - 1 LOOP
    FOR j IN i + 1 .. v_idx LOOP
      IF v_arr(i) = v_arr(j) THEN
        v_arr(j) := NULL;
      END IF;
    END LOOP;
  END LOOP;

  -- 3. 重組字串
  FOR i IN 1 .. v_idx LOOP
    IF v_arr(i) IS NOT NULL THEN
      v_result := v_result || p_sep || v_arr(i);
    END IF;
  END LOOP;

  RETURN LTRIM(v_result, p_sep);  -- 移除前導分隔符
END my_replace;
/

二、測試案例

-- 基本去重
SELECT my_replace('13,14,13,444', ',') FROM dual;  -- 13,14,444

-- 含空白
SELECT my_replace(' a , b , a , c ', ',') FROM dual;  -- a,b,c

-- 管線分隔符
SELECT my_replace('apple|orange|apple|banana', '|') FROM dual;  -- apple|orange|banana

-- 空字串/NULL
SELECT my_replace('', ',') FROM dual;       -- (null)
SELECT my_replace(NULL, ',') FROM dual;     -- (null)

-- 單一值
SELECT my_replace('only', ',') FROM dual;   -- only

三、效能優化版（12c+ 使用關聯陣列 + 唯一索引模擬）

CREATE OR REPLACE FUNCTION my_replace_fast(
  p_str IN VARCHAR2,
  p_sep IN VARCHAR2 DEFAULT ','
) RETURN VARCHAR2 IS
  v_str    VARCHAR2(32767) := p_str;
  v_idx    PLS_INTEGER := 0;
  v_start  PLS_INTEGER := 0;
  v_end    PLS_INTEGER;
  TYPE arr_t IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;
  v_arr    arr_t;
  -- 模擬 Set：用第二個陣列記錄已見值的索引
  TYPE seen_t IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(4000);
  v_seen   seen_t;
  v_result VARCHAR2(32767);
BEGIN
  IF v_str IS NULL THEN RETURN NULL; END IF;

  LOOP
    v_idx := v_idx + 1;
    v_end := INSTR(v_str, p_sep, 1, v_idx);
    IF v_end = 0 THEN EXIT; END IF;
    v_arr(v_idx) := TRIM(SUBSTR(v_str, v_start + 1, v_end - v_start - 1));
    v_start := v_end;
  END LOOP;
  v_arr(v_idx) := TRIM(SUBSTR(v_str, v_start + 1));

  -- 單次遍歷去重（O(N)）
  FOR i IN 1 .. v_idx LOOP
    IF NOT v_seen.EXISTS(v_arr(i)) THEN
      v_seen(v_arr(i)) := 1;
      v_result := v_result || p_sep || v_arr(i);
    END IF;
  END LOOP;

  RETURN LTRIM(v_result, p_sep);
END my_replace_fast;
/

複雜度：基礎版 O(N²)，優化版 O(N)；當分隔片段 > 1000 時差異明顯。

四、12c+ 純 SQL 寫法（無需函數，遞迴 CTE）

WITH split(str, sep, lvl, token, rest) AS (
  SELECT p_str, p_sep, 1,
         TRIM(SUBSTR(p_str, 1, INSTR(p_str, p_sep) - 1)),
         SUBSTR(p_str, INSTR(p_str, p_sep) + 1)
  FROM   (SELECT '13,14,13,444' p_str, ',' p_sep FROM dual)
  UNION ALL
  SELECT str, sep, lvl + 1,
         TRIM(SUBSTR(rest, 1, INSTR(rest, sep) - 1)),
         SUBSTR(rest, INSTR(rest, sep) + 1)
  FROM   split
  WHERE  rest IS NOT NULL
),
dedup AS (
  SELECT token, MIN(lvl) AS first_lvl
  FROM   split
  WHERE  token IS NOT NULL
  GROUP BY token
)
SELECT LISTAGG(token, ',') WITHIN GROUP (ORDER BY first_lvl) AS result
FROM   dedup;

適合一次性查詢、不想建立函數物件的場景。

五、應用場景擴展

將 my_replace / my_replace_fast 納入共用套件 pkg_string_utils，統一管理版本與測試，讓字串去重邏輯在全系統複用、可控、可測試。