功能:去除分隔符分隔字符串中的重複項。
示例: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。