Oracle 字串截取函數：避免半個中文字元的 SUBSTR_GB 實作

問題背景

SUBSTR 以 **字元** 為單位，SUBSTRB 以 **位元組** 為單位。中文環境 (ZHS16GBK/AL32UTF8) 下，中文佔 2~3 Bytes。**指定位元組長度截取時，極易截斷成半個中文字元**，導致顯示亂碼或應用報錯。

解決方案：SUBSTR_GB 函數

CREATE OR REPLACE FUNCTION substr_gb (
    p_str    IN VARCHAR2,
    p_begin  IN INTEGER,
    p_length IN INTEGER
) RETURN VARCHAR2
IS
    v_result VARCHAR2(4000);
BEGIN
    -- 邊界檢查
    IF p_str IS NULL OR p_length <= 0 THEN
        RETURN NULL;
    END IF;

    -- 核心邏輯：嘗試截取 p_length 位元組，若等於截取 p_length+1 位元組長度，
    -- 表示第 p_length+1 位元組是雙位元字元的後半部，需退一位
    IF LENGTHB(SUBSTRB(p_str, p_begin, p_length)) =
       LENGTHB(SUBSTRB(p_str, p_begin, p_length + 1)) THEN
        v_result := SUBSTRB(p_str, p_begin, p_length - 1);
    ELSE
        v_result := SUBSTRB(p_str, p_begin, p_length);
    END IF;

    RETURN v_result;
END substr_gb;
/

測試驗證

-- 環境：NLS_CHARACTERSET = ZHS16GBK (中文 2 Bytes)
SELECT substr_gb('ABC中文測試', 1, 6)  FROM dual; -- 'ABC中' (6 Bytes: A B C 中(2) 文(2) → 退一位)
SELECT substr_gb('ABC中文測試', 1, 7)  FROM dual; -- 'ABC中文' (7 Bytes 完整包含 '文')
SELECT substr_gb('ABC中文測試', 4, 4)  FROM dual; -- '中文' (從第 4 Byte 開始)

-- UTF-8 環境 (中文 3 Bytes) 同樣適用
-- 原理：LENGTHB 計算實際位元組長度，比較是否因截斷雙位元字元導致長度不變

進階版：支援字元語意與位元組語意雙模式

CREATE OR REPLACE FUNCTION substr_smart (
    p_str       IN VARCHAR2,
    p_start     IN INTEGER,      -- 起始位置 (1-based)
    p_length    IN INTEGER,      -- 長度
    p_by_byte   IN BOOLEAN DEFAULT TRUE  -- TRUE: 位元組語意, FALSE: 字元語意
) RETURN VARCHAR2
IS
    v_str VARCHAR2(4000);
BEGIN
    IF p_by_byte THEN
        -- 位元組語意：呼叫 substr_gb 邏輯
        IF LENGTHB(SUBSTRB(p_str, p_start, p_length)) =
           LENGTHB(SUBSTRB(p_str, p_start, p_length + 1)) THEN
            RETURN SUBSTRB(p_str, p_start, p_length - 1);
        ELSE
            RETURN SUBSTRB(p_str, p_start, p_length);
        END IF;
    ELSE
        -- 字元語意：直接用 SUBSTR (Oracle 內建已正確處理多位元字元)
        RETURN SUBSTR(p_str, p_start, p_length);
    END IF;
END substr_smart;
/

12c+ 現代替代：SUBSTR 配合字元語意

Oracle 12c 起，**SUBSTR / INSTR 等函數在字元語意下已完美支援多位元字元**，無需自訂函數。**僅當業務邏輯強制要求「位元組長度截取」且必須避免半字元時**，才需要 substr_gb。

效能提示

• 函數內雙重 SUBSTRB + LENGTHB，單次呼叫開銷微小。
• 大量資料 (百萬行) 建議：**改用字元語意邏輯** 或 **PL/SQL 批次處理**，避免 SQL 引擎頻繁切換到 PL/SQL 引擎 (Context Switch)。
• 可建立 **Function-Based Index** 支援查詢：CREATE INDEX idx_substr_gb ON t(substr_gb(col,1,20));。

結語

substr_gb 以 **位元組長度比較** 精準偵測半字元截斷，**通用於 GBK/UTF8/Big5 等所有多位元字元集**。現代開發優先使用 **字元語意 (SUBSTR/LENGTH)**，僅在報表固定寬度匯出、Legacy 介面對接等**強制位元組長度**場景使用此函數。封裝至通用套件 PKG_STR 統一管理。