萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server SUBSTRING 函數深度解析:負數起始位置行為與實測驗證

語法定義

SUBSTRING(expression, start, length)
-- expression: 字串/二進位/資料欄
-- start: 1-based 起始位置 (INT)
-- length: 截取長度 (INT)

負數/零 start 參數行為實測

測試資料:DWMC = 'ABCDEFG' (長度 7)

startlength結果解釋
13ABC正常:從第 1 字元取 3 個
03ABstart=0 視為位置 1,但長度扣除 1 (邏輯:start+length-1 ≤ 實際長度)
-13Astart=-1 → 邏輯位置 0,僅能取 1 字元
-23(空字串)start=-2 → 邏輯位置 -1,無有效字元
-93(空字串)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()。

最佳實踐

  1. 永遠使用正數 start:start = CASE WHEN @pos <= 0 THEN 1 ELSE @pos END。
  2. 需從右截取用 RIGHT(str, n) 或 SUBSTRING(str, LEN(str)-n+1, n)。
  3. 動態 start 參數務必加 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;
copyright © 萬盛學電腦網 all rights reserved