Oracle SUBSTR 函數完全指南：左取、右取、中間截取與進階用法

為何 Oracle 沒有 LEFT/RIGHT 函數？

Oracle 設計哲學偏好單一強大函數而非多個專用函數。SUBSTR 通過靈活的參數組合，完全涵蓋 LEFT、RIGHT、MID 等功能，減少函數名稱空間佔用，且行為跨平台一致 (PL/SQL、SQL 通用)。

語法原型

SUBSTR(string, position [, substring_length])

• string：來源字串 (字面值、欄位、運算式)。
• position：起始位置 從 1 開始計算。
  • > 0：從左向右數 (第 1 字元 = 1)。
  • < 0：從右向左數 (最後 1 字元 = -1)。
  • = 0：視同 1 (從左起第一個)。
  • 小數部分直接捨去 (非四捨五入)。
• substring_length：擷取長度 (字元數)。
  • 省略：擷取至字串結尾。
  • > 0：指定字元數，超過字串長度則至結尾。
  • ≤ 0：回傳 NULL (空字串在 Oracle 視為 NULL)。
  • 小數捨去。

經典用法速查表

字元 vs 位元組語意

• SUBSTR：**按字元計算**，中英文各佔 1 字元。SUBSTR('智能ABC', 2, 2) = '能A'。
• SUBSTRB：**按位元組計算**，適用於定長位元組切割。SUBSTRB('智能ABC', 3, 4) = '能AB' (假設 AL32UTF8 中文 3 bytes)。
• SUBSTRC：按 Unicode 完整字元 (Code Point) 計算，處理增補字元 (Emoji 等) 正確。
• SUBSTR2：按 UCS-2 代碼單元計算。
• SUBSTR4：按 UCS-4 代碼單元計算。

建議：現代系統統一使用 AL32UTF8，優先用 SUBSTR (字元語意)；僅在處理二進位資料、舊系統介接時用 SUBSTRB。

實戰進階技巧

1. 動態截取：去除固定前綴/後綴

-- 移除前綴 'ERR_'
SUBSTR(err_code, 5)  -- 從第 5 字元到結尾

-- 移除後綴 '_LOG'
SUBSTR(log_name, 1, LENGTH(log_name) - 4)

2. 結合 INSTR 定位分隔符

-- 取 '@' 前的帳號部份
SUBSTR(email, 1, INSTR(email, '@') - 1)

-- 取最後一個 '/' 後的檔名
SUBSTR(file_path, INSTR(file_path, '/', -1) + 1)

3. 正則表達式替代 (10g+)

-- 取第一組數字
REGEXP_SUBSTR('Order 12345 Date', '\d+')

-- 取最後一組數字
REGEXP_SUBSTR('Order 12345 Date 2023', '\d+', 1, 1, 'i', 1) -- 需配合其他技巧

4. PL/SQL 批量處理效能

-- 集合批量處理範例
DECLARE
  TYPE t_arr IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
  v_src t_arr;
  v_tgt t_arr;
BEGIN
  v_src(1) := 'ABCDEFG'; v_src(2) := 'HIJKLMN';
  FOR i IN v_src.FIRST .. v_src.LAST LOOP
    v_tgt(i) := SUBSTR(v_src(i), -3); -- 取右 3 碼
  END LOOP;
  -- FORALL 寫入資料表...
END;

常見錯誤與避坑指南

• 位置從 1 開始：習慣 0-based 索引的開發者易誤用 0，導致結果偏移或 NULL。
• 負位置不包含長度時預設至結尾：SUBSTR(str, -3) 正確；但 SUBSTR(str, -3, 0) 回傳 NULL。
• NULL 輸入傳播：任一參數為 NULL，結果為 NULL。需用 NVL 或 COALESCE 防護。
• 字元集語意不一致：資料庫 AL32UTF8 但客戶端 ZHS16GBK 導致 LENGTH/SUBSTR 字元數計算差異，統一用 SUBSTR (字元語意) 並確保 NLS_LANG 正確。

快速對照：其他資料庫對應函數

掌握 SUBSTR 的正負位置與長度規則，即可在 Oracle 中靈活完成所有字串截取需求，無需羨慕其他資料庫的專用函數。