SQL Server 字串截取實戰：SUBSTRING + PATINDEX 組合技巧

核心函數速查

組合範例：擷取中文日期片段

DECLARE @a VARCHAR(50) = '2009年7月15日星期五';

-- 1. 直接擷取固定位置 (年份前 4 碼)
SELECT SUBSTRING(@a, 1, 4);  -- '2009'

-- 2. 找關鍵字位置
DECLARE @b INT = PATINDEX('%日%', @a);  -- 10

-- 3. 動態擷取 '7月15日' (第 6 碼起，長度 = '日' 位置 - 5)
SELECT SUBSTRING(@a, 6, @b - 5);  -- '7月15日'

進階模式：PATINDEX 萬用字元

-- 找第一個數字位置
PATINDEX('%[0-9]%', @str);

-- 找第一個非數字位置
PATINDEX('%[^0-9]%', @str);

-- 找特定格式 (如手機號)
PATINDEX('%[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]%', @str);

常見陷阱

• SUBSTRING 的 start 為 **1-based**，.NET/其他語言多為 0-based，移植時易出錯。
• PATINDEX 模式**必須包含 %**，否則語法錯誤 (除搜尋首/尾字元)。
• 對 TEXT/NTEXT 型別需先 CAST(... AS VARCHAR(MAX))。
• PATINDEX 不支援正則運算式完整語法，僅類 LIKE 模式。

現代替代方案 (SQL 2016+)

• STRING_SPLIT、STRING_AGG 處理分隔符字串。
• JSON_VALUE、JSON_QUERY 處理結構化字串。
• 複雜解析改用 **CLR 正則運算式** 或 **Python/R 外部腳本**。

實務建議

1. 固定格式優先用 SUBSTRING + 固定位置。
2. 關鍵字定位用 PATINDEX / CHARINDEX。
3. 避免在 WHERE 條件對大欄位用函數 (索引失效)，改用計算欄位 + 索引。