語法定義
SUBSTRING ( expression, start, length )
| 參數 | 資料型別 | 說明 |
|---|
| expression | 字串/二進位型別 | 來源運算式：欄位、變數、字串常值、純量函數回傳值。**不可含聚合函數**。 |
| start | INT | **1-based** 起始位置。1=第一個字元。0 或負數視為「虛擬位置」，見下表行為。 |
| length | INT | 擷取長度 (字元/位元組數)。超出實際長度不報錯，回傳至字串結尾。 |
起始位置特殊值行為對照表
測試字串 'ABCDEFGHIJ' (長度 10)：
| start | length | 結果 | 解釋 |
|---|
| 1 | 3 | ABC | 正常：第 1 字元起取 3 碼 |
| 0 | 3 | AB | 從位置 0 起算：位置 0→虛擬, 1→A, 2→B → 取 2 碼 |
| -1 | 3 | A | 位置 -1→虛擬, 0→虛擬, 1→A → 取 1 碼 |
| -2 | 3 | (空字串) | 位置 -2,-1,0 皆為虛擬，第 1 字元在相對位置 3，length=3 尚未到達 |
| -9 | 3 | (空字串) | 同理，實際字元起始相對位置 > length |
核心規則：SUBSTRING 在**邏輯座標軸**上從 start 向右量取 length。字串實際內容佔據座標 1 到 L。start ≤ 0 時，有效擷取區間為 MAX(1, start) ... start+length-1 與 1 ... L 的交集。
實戰範例
1. 固定格式擷取：身分證字號去驗證碼
SELECT SUBSTRING(IdNo, 1, 9) AS BaseNo FROM Customers; -- A12345678
2. 動態長度：逗號分隔欄位取第一段
SELECT SUBSTRING(Data, 1, CHARINDEX(',', Data + ',') - 1) AS FirstPart FROM RawData;
3. 反向擷取：取最後 N 字元 (配合 LEN)
SELECT SUBSTRING(Col, LEN(Col) - 3 + 1, 4) AS Last4 FROM Table1;
-- 或 SQL 2012+ RIGHT(Col, 4)
4. 二進位資料擷取
SELECT SUBSTRING(ImageData, 1, 4) AS HeaderBytes FROM Images; -- 讀取檔案頭
常見錯誤與避坑
| 錯誤 | 原因 | 修正 |
|---|
SUBSTRING(col, 0, 5) 期望取前 5 碼 | start=0 只取前 4 碼 | 用 start=1 |
SUBSTRING(col, -5, 10) 期望倒數 | 負數非倒數索引 | 用 SUBSTRING(col, LEN(col)-5+1, 5) 或 RIGHT(col, 5) |
對 text/ntext/image 直接用 SUBSTRING | 舊型別需 TEXTPTR + READTEXT | 改用 varchar(max)/varbinary(max) 直接支援 |
| 聚合內用 SUBSTRING | 參數不可含聚合函數 | 先聚合再擷取，或用 CTE 分層 |
效能提示
- SARGable：
WHERE SUBSTRING(col, 1, 3) = 'ABC' **無法使用索引** (函數在欄位上)。改用 WHERE col LIKE 'ABC%' 或計算欄位+索引。
- 大量資料擷取：投影欄位時僅擷取所需長度，減少網路傳輸：
SELECT SUBSTRING(BigCol, 1, 100) AS Preview FROM BigTable。
- 二進位擷取：
varbinary(max) 同樣支援，單位為**位元組**而非字元。
相關函數快查
| 函數 | 用途 | 備註 |
|---|
| LEFT(str, n) | 取左 n 字元 | 等同 SUBSTRING(str, 1, n) |
| RIGHT(str, n) | 取右 n 字元 | SQL 2012+ |
| CHARINDEX(sub, str) | 尋找子字串位置 | 配合 SUBSTRING 動態擷取 |
| PATINDEX(pattern, str) | 萬用字元尋找 | 支援 %[0-9]% 等模式 |
| STUFF(str, start, len, repl) | 刪除+插入 | 字串修改利器 |
