SUBSTR (Oracle/MySQL/PostgreSQL) / SUBSTRING (SQL Server/Standard SQL) 是字串截取的核心函數。本文詳細解析參數意義、正負索引行為、與 Java substring 的差異,並提供跨資料庫對照表。
-- Oracle / MySQL / PostgreSQL / DB2
SUBSTR(string, start_position, [length])
-- SQL Server / ANSI SQL
SUBSTRING(string, start, length)
| 參數 | 說明 | 特殊規則 |
|---|---|---|
| string | 來源字串 (欄位/變數/常數) | |
| start | 起始位置,**1-based** (第一個字元位置為 1) | 正數:從左往右數 負數:從右往左數 (Oracle/MySQL/PostgreSQL 支援) 0:視同 1 (Oracle/標準 SQL) |
| length | 截取長度 (選填) | 省略 = 截取至字串結尾; 負數/零 → 回傳空字串 (視資料庫而定) |
-- 基礎截取
SELECT SUBSTR('abcdefg', 3, 4); -- 'cdef' (從第 3 字元取 4 個)
SELECT SUBSTRING('abcdefg', 3, 4); -- 'cdef' (SQL Server)
-- 負數起始位置 (Oracle/MySQL/PostgreSQL)
SELECT SUBSTR('abcdefg', -3, 4); -- 'efg' (從倒數第 3 字元 'e' 開始取 4 個,實際只取到結尾)
-- SQL Server SUBSTRING **不支援負數起始**,需自行計算:SUBSTRING(str, LEN(str)+start, length)
-- 起始為 0
SELECT SUBSTR('abcdefg', 0, 3); -- 'abc' (Oracle: 0 視同 1)
SELECT SUBSTRING('abcdefg', 0, 3); -- 'ab' (SQL Server: 0 為第 0 位,實際從 1 開始取 3 個 → 'abc'? 實測 SQL Server start=0 視同 1)
-- 實測 SQL Server 2019: SUBSTRING('abcdefg',0,3) → 'abc' (同 start=1)
| 資料庫 | 函數名 | 支援負數 start | start=0 行為 | length 省略 | 超出長度處理 |
|---|---|---|---|---|---|
| Oracle | SUBSTR | ✅ 從右往左 | 視同 1 | 至結尾 | 回傳可用部分,不報錯 |
| MySQL | SUBSTR / SUBSTRING | ✅ 從右往左 | 視同 1 | 至結尾 | 回傳可用部分 |
| PostgreSQL | SUBSTRING / SUBSTR | ✅ 從右往左 | 視同 1 | 至結尾 | 回傳可用部分 |
| SQL Server | SUBSTRING | ❌ (需自算) | 視同 1 | **必填** (不可省略) | 回傳可用部分 |
| DB2 | SUBSTR | ✅ | 視同 1 | 至結尾 | 回傳可用部分 |
| Access | Mid | ❌ | 錯誤 | 必填 | 錯誤 |
-- 取右邊 3 碼
SELECT RIGHT('abcdefg', 3); -- 'efg' (最簡單)
SELECT SUBSTRING('abcdefg', LEN('abcdefg') - 3 + 1, 3); -- 通用寫法
-- 取倒數第 3 碼開始的 4 碼 (對應 Oracle SUBSTR(str, -3, 4))
DECLARE @str VARCHAR(20) = 'abcdefg';
SELECT SUBSTRING(@str, LEN(@str) - 3 + 1, 4); -- 'efg'
| 特性 | SQL SUBSTR/SUBSTRING | Java substring |
|---|---|---|
| 索引基準 | **1-based** (第一字元=1) | **0-based** (第一字元=0) |
| 參數模式 | (start, length) | (beginIndex, endIndex) **結束索引不包含** |
| 負數索引 | 部份支援 (Oracle/MySQL/PG) | 不支援 (拋出例外) |
| 範例 | SUBSTR('abc',1,2)='ab' | "abc".substring(0,2)='ab' |
| 結束邊界 | 長度超出截至結尾 | endIndex > length 拋出 StringIndexOutOfBoundsException |
-- 1. 固定寬度欄位解析 (如身分證、電話)
SELECT SUBSTRING(Phone, 1, 3) AS AreaCode, SUBSTRING(Phone, 4, 8) AS Number FROM Customers;
-- 2. 移除前後綴
SELECT SUBSTRING(Col, 2, LEN(Col)-2) FROM Table; -- 去除首尾各 1 字元
-- 3. 截取最後 N 碼
SELECT RIGHT(Col, 4) AS Last4; -- SQL Server/MySQL/Oracle 皆支援 RIGHT()
-- 4. 動態截取 (搭配 CHARINDEX/PATINDEX)
SELECT SUBSTRING(Url, CHARINDEX('://', Url)+3, LEN(Url)) FROM Logs; -- 移除協定頭
SUBSTRING 在 WHERE 條件中使用會導致**索引無法尋找** (SARGable 失效)。WHERE Col LIKE 'prefix%' 或使用**計算欄位 + 索引**:ALTER TABLE T ADD Prefix3 AS SUBSTRING(Col,1,3) PERSISTED; CREATE INDEX IX_Prefix3 ON T(Prefix3);熟練掌握字串截取函數的跨平台差異,能讓您在異構資料庫環境、ETL 轉換、遷移專案中游刃有餘。