DB2 字串分割 實作：遞迴 CTE (SQL) vs UDF 函數 兩種方案完整比較

需求場景

輸入字串 'a,b,c'，以逗號分隔，輸出三列：

a
b
c

方案一：純 SQL 遞迴 CTE（推薦、無需建立物件）

WITH RECURSIVE SPLIT(STR, POS, TOKEN) AS (
    -- Anchor: 第一個 token
    SELECT 
        'a,b,c,' AS STR,           -- 尾端加分隔符簡化邏輯
        1 AS POS,
        SUBSTR('a,b,c,', 1, LOCATE(',', 'a,b,c,', 1) - 1) AS TOKEN
    FROM SYSIBM.SYSDUMMY1
    
    UNION ALL
    
    -- Recursive: 後續 token
    SELECT 
        STR,
        LOCATE(',', STR, POS) + 1 AS POS,
        SUBSTR(STR, POS, LOCATE(',', STR, POS) - POS) AS TOKEN
    FROM SPLIT
    WHERE LOCATE(',', STR, POS) > 0
)
SELECT TOKEN FROM SPLIT WHERE TOKEN <> '';

關鍵技巧：在字串尾端人為加上分隔符 ','，使每個 token 皆為「起始位置 → 下一分隔符」的固定模式，避免處理最後一個 token 的特殊邏輯。

方案二：SQL PL 表函數 (Table UDF)

CREATE OR REPLACE FUNCTION SPLIT_STR(INPUT_STR VARCHAR(4000), DELIM VARCHAR(10))
RETURNS TABLE (TOKEN VARCHAR(4000))
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
    DECLARE POS INT DEFAULT 1;
    DECLARE NEXT_POS INT;
    DECLARE TOKEN VARCHAR(4000);
    DECLARE STR_WITH_DELIM VARCHAR(4010) DEFAULT INPUT_STR || DELIM;
    
    WHILE TRUE DO
        SET NEXT_POS = LOCATE(DELIM, STR_WITH_DELIM, POS);
        IF NEXT_POS = 0 THEN LEAVE; END IF;
        SET TOKEN = SUBSTR(STR_WITH_DELIM, POS, NEXT_POS - POS);
        IF TOKEN <> '' THEN
            PIPE (TOKEN);
        END IF;
        SET POS = NEXT_POS + LENGTH(DELIM);
    END WHILE;
END

呼叫方式：

SELECT TOKEN FROM TABLE(SPLIT_STR('a,b,c', ',')) AS T;

方案三：Oracle 風格 PL/SQL 集合函數（僅供參考，DB2 不支援）

原文提供的 Oracle 寫法使用 CREATE TYPE ... TABLE OF VARCHAR2 + PIPELINED 函數，DB2 並無完全對應語法，需改用上述 SQL Table Function 或 Java UDF。

效能與適用性比較

進階：處理連續分隔符、前後空白、NULL 輸入

-- CTE 版本增強
WITH RECURSIVE SPLIT(STR, POS, TOKEN) AS (
    SELECT 
        TRIM(BOTH ',' FROM INPUT_STR) || ',' AS STR,  -- 去除首尾分隔符再加回一個
        1 AS POS,
        NULL AS TOKEN
    FROM (VALUES (COALESCE(NULLIF('  a,,b,c  ', ''), ''))) AS V(INPUT_STR)
    
    UNION ALL
    
    SELECT 
        STR,
        LOCATE(',', STR, POS) + 1,
        TRIM(SUBSTR(STR, POS, LOCATE(',', STR, POS) - POS))
    FROM SPLIT
    WHERE LOCATE(',', STR, POS) > 0
)
SELECT TOKEN FROM SPLIT WHERE TOKEN IS NOT NULL AND TOKEN <> '';

總結建議

1. 臨時/一次性查詢：直接用 遞迴 CTE，零部署、可攜性最強。
2. 高頻復用、需封裝業務邏輯：建立 SQL Table UDF，效能優於 CTE，維護性佳。
3. 超大字串 (＞32KB)、複雜解析規則：考慮 Java UDF 或應用層預處理。

DB2 11.5+ 亦可嘗試 REGEXP_SUBSTR、SPLIT_PART (相容函數) 簡化寫法，但核心邏輯仍建立在集合運算思維上。