SUBSTRING(expression, start, length)
-- expression: 字串/二進位/資料欄
-- start: 1-based 起始位置 (INT)
-- length: 截取長度 (INT)
測試資料:DWMC = 'ABCDEFG' (長度 7)
| start | length | 結果 | 解釋 |
|---|---|---|---|
| 1 | 3 | ABC | 正常:從第 1 字元取 3 個 |
| 0 | 3 | AB | start=0 視為位置 1,但長度扣除 1 (邏輯:start+length-1 ≤ 實際長度) |
| -1 | 3 | A | start=-1 → 邏輯位置 0,僅能取 1 字元 |
| -2 | 3 | (空字串) | start=-2 → 邏輯位置 -1,無有效字元 |
| -9 | 3 | (空字串) | start ≤ -len 直接回傳空字串 |
SQL Server 將字串視為從 **位置 1 開始** 的序列。start ≤ 0 時,實際起始位置計算為 start,但截取範圍受限於 [1, LEN(expression)]。等效公式:
實際起始 = MAX(1, start)
實際結束 = MIN(LEN(expression), start + length - 1)
若 實際起始 > 實際結束 → 回傳空字串
| 資料庫 | 負數 start 行為 | 備註 |
|---|---|---|
| SQL Server | 視為位置 ≤ 0,截取範圍向右延伸,常回傳空或部分 | 非標準,極易產生 Bug |
| PostgreSQL / Oracle | 負數 = 從右往左計算 (如 -1 = 最後一字元) | 符合 SQL:2003 標準 |
| MySQL | 負數 = 從右往左計算 | 同標準 |
警告:移植 SQL 時務必重寫負數 start 邏輯,或改用 RIGHT()/LEFT()/SUBSTRING_INDEX()。
start = CASE WHEN @pos <= 0 THEN 1 ELSE @pos END。RIGHT(str, n) 或 SUBSTRING(str, LEN(str)-n+1, n)。NULLIF/ISNULL 防護。-- 取最後 N 字元
SELECT RIGHT(col, 5) FROM t;
-- 取倒數第 3 到倒數第 1 字元
SELECT SUBSTRING(col, LEN(col)-2, 3) FROM t;
-- 安全版 SUBSTRING (自動修正負數)
CREATE FUNCTION dbo.SafeSubstring(@s NVARCHAR(MAX), @start INT, @len INT)
RETURNS NVARCHAR(MAX) AS BEGIN
IF @start <= 0 SET @start = 1;
IF @len < 0 SET @len = 0;
RETURN SUBSTRING(@s, @start, @len);
END;