SQL Server CHARINDEX 與 PATINDEX 字串搜尋函數深度解析

CHARINDEX 與 PATINDEX 皆用於在字串中搜尋子字串位置，回傳起始位置 (1-based)，找不到回傳 0。核心差異在於 PATINDEX 支援萬用字元，CHARINDEX 不支援。

CHARINDEX

CHARINDEX(expressionToFind, expressionToSearch [, start_location])

• expressionToFind：要搜尋的字元/字串。
• expressionToSearch：被搜尋的字串。
• start_location：選擇性，開始搜尋位置。

範例：

CHARINDEX('SQL', 'Microsoft SQL Server')  -- 回傳 11 ('S' 位置)
CHARINDEX('7.0', 'Microsoft SQL Server 2000')  -- 回傳 0 (找不到)
CHARINDEX('Server', 'Microsoft SQL Server', 15)  -- 從第 15 字元開始找

PATINDEX

PATINDEX('%pattern%', expressionToSearch)

• 模式字串 必須以 % 包裹 (如 '%SQL%')。
• 支援萬用字元：% (任意長度)、_ (單一字元)、[range] (字元範圍)、[^range] (排除範圍)。

範例：

PATINDEX('%SQL%', 'Microsoft SQL Server')           -- 11
PATINDEX('%[0-9]%', 'abc123')                       -- 4 (第一個數字位置)
PATINDEX('%[^a-z]%', 'abc123')                      -- 4 (第一個非小寫字母)
PATINDEX('%[0-9][0-9][0-9]%', 'abc123def')          -- 4 (第一個三位數字序列)

效能與使用建議

• 單純子字串定位用 CHARINDEX，語法簡單、效能略佳。
• 需模式比對 (如「第一個數字」、「郵遞區號格式」、「特定前綴」) 用 PATINDEX。
• 大量資料頻繁搜尋建議建立全文檢索索引 (Full-Text Search) 或計算欄位持久化。