萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

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);
  CURRENTINDEX   NUMBER;
  STARTINDEX     NUMBER;
  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;
  STR := OLDSTR;
  CURRENTINDEX := 0;
  STARTINDEX := 0;

  -- 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. 重組字符串
  FOR I IN 1 .. CURRENTINDEX LOOP
    IF ARR(I) IS NOT NULL THEN
      RESULT := RESULT || SIGN || ARR(I);
      ARR(I) := ''; -- 清理
    END IF;
  END LOOP;

  RETURN SUBSTR(RESULT, 2); -- 去除首個分隔符
END MYREPLACE;
/

適用於標籤清洗、ID 列表去重等場景。Oracle 12c+ 可用 LISTAGG(DISTINCT ...) 簡化,但本函數兼容 10g/11g。

copyright © 萬盛學電腦網 all rights reserved