Oracle 字符串分割函數 fn_split：將逗號分隔列轉為多行

Oracle 字符串分割函數 fn_split：將逗號分隔列轉為多行

使用範例

-- 數值分割
SELECT to_number(strvalue) AS value FROM TABLE(fn_split('1,2,3', ','));

-- 字符串分割
SELECT to_char(strvalue) AS value FROM TABLE(fn_split('aa,bb,cc', ','));

函數完整代碼

-- 先建立物件類型
CREATE OR REPLACE TYPE ty_row_str_split AS OBJECT (strValue VARCHAR2(4000));
/
CREATE OR REPLACE TYPE ty_tbl_str_split AS TABLE OF ty_row_str_split;
/

-- 分割函數
CREATE OR REPLACE FUNCTION fn_split(
    p_str        IN VARCHAR2,
    p_delimiter  IN VARCHAR2
) RETURN ty_tbl_str_split IS
    j      INT := 0;          -- 找到分隔符的位置
    i      INT := 1;          -- 當前搜尋起始位置
    len    INT := 0;          -- 原字符串長度
    len1   INT := 0;          -- 分隔符長度
    str    VARCHAR2(4000);    -- 暫存單元內容
    str_split ty_tbl_str_split := ty_tbl_str_split(); -- 結果集
BEGIN
    len  := LENGTH(p_str);
    len1 := LENGTH(p_delimiter);

    WHILE j < len LOOP
        j := INSTR(p_str, p_delimiter, i);
        IF j = 0 THEN
            -- 最後一個單元
            j := len;
            str := SUBSTR(p_str, i);
            str_split.EXTEND;
            str_split(str_split.COUNT) := ty_row_str_split(str);
            IF i >= len THEN EXIT; END IF;
        ELSE
            -- 找到分隔符
            str := SUBSTR(p_str, i, j - i);
            i := j + len1;  -- 下次搜尋起始位置
            str_split.EXTEND;
            str_split(str_split.COUNT) := ty_row_str_split(str);
        END IF;
    END LOOP;
    RETURN str_split;
END fn_split;
/

關鍵點說明

• 返回類型為嵌套表 ty_tbl_str_split，可直接在 SQL 中用 TABLE() 轉為關係型結果集。
• 支援多字符分隔符（如 ', '、'||'）。
• 處理邊界情況：字符串以分隔符結尾、連續分隔符、無分隔符等。
• 使用 INSTR(..., i) 指定起始搜尋位置，避免重複掃描，效能優於遞歸寫法。

應用場景

ETL 數據清洗、前端傳入 ID 列表解析、報表參數展開等「一列轉多行」需求。