您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

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

編輯：關於SqlServer

字串搜尋是 T-SQL 開發的高頻需求。CHARINDEX 與 PATINDEX 是兩大核心函數，前者精確匹配、後者支援萬用字元。本文詳細解析語法、差異、效能與五大實戰應用場景。

函數語法與核心差異

特性	CHARINDEX	PATINDEX
語法	`CHARINDEX(expressionToFind, expressionToSearch [, start_location])`	`PATINDEX('%pattern%', expressionToSearch)`
萬用字元支援	不支援	支援 `%` `_` `[]` `[^]`
回傳值	找到 → 起始位置 (1-based)；未找到 → 0	同左
資料型別支援	char/varchar/nchar/nvarchar/text/ntext	同左 (不支援 varchar(max)/nvarchar(max) 直接輸入，需轉換)
大小寫敏感度	取決於定序 (Collation)	同左

實戰範例 1：擷取姓氏 (Northwind Customers)


-- ContactName = 'Maria Anders' → 擷取 'Anders'
SELECT TOP 5 
    ContactName,
    SUBSTRING(ContactName, CHARINDEX(' ', ContactName) + 1, LEN(ContactName)) AS LastName
FROM Northwind.dbo.Customers;
-- 結果: Anders, Trujillo, Moreno, Hardy, Berglund

關鍵技巧：CHARINDEX 找到空格位置，+1 跳過空格，SUBSTRING 截取剩餘字串。

實戰範例 2：計算包含特定字串的列數


-- 計算 Address 包含 'Road' 或 'Rd' 的客戶數
SELECT COUNT(*) AS RoadAddressCount
FROM Northwind.dbo.Customers
WHERE CHARINDEX('Road', Address) > 0 
   OR CHARINDEX('Rd', Address) > 0;

實戰範例 3：PATINDEX 萬用字元應用


-- 找出電話號碼包含 '555' 開頭的客戶 (格式: (xxx) xxx-xxxx)
SELECT CustomerID, Phone
FROM Northwind.dbo.Customers
WHERE PATINDEX('%[ (]555[ )-]%', Phone) > 0;

-- 找出欄位包含數字的列
SELECT * FROM MyTable WHERE PATINDEX('%[0-9]%', MyColumn) > 0;

-- 找出以 'A' 開頭、以 'Z' 結尾的字串
SELECT * FROM MyTable WHERE PATINDEX('A%Z', MyColumn) > 0;

實戰範例 4：搭配 STUFF/REPLACE 進行字串插入/取代


-- 在 'SQLServer' 第 3 字元後插入空格 → 'SQL Server'
DECLARE @str VARCHAR(20) = 'SQLServer';
SELECT STUFF(@str, CHARINDEX('S', @str, 2), 0, ' ');  -- 找第二個 S 的位置

-- 移除字串中的所有數字
DECLARE @data VARCHAR(100) = 'Order123Detail456';
WHILE PATINDEX('%[0-9]%', @data) > 0
    SET @data = STUFF(@data, PATINDEX('%[0-9]%', @data), 1, '');
SELECT @data;  -- 'OrderDetail'

實戰範例 5：分割字串 (搭配 XML/STRING_SPLIT)


-- SQL 2016+ 內建函數
SELECT value FROM STRING_SPLIT('1,2,3,4,5', ',');

-- 舊版相容寫法 (使用 CHARINDEX 迴圈)
DECLARE @csv VARCHAR(8000) = '1,2,3,4,5', @pos INT, @val VARCHAR(100);
WHILE LEN(@csv) > 0
BEGIN
    SET @pos = CHARINDEX(',', @csv);
    IF @pos = 0 SET @pos = LEN(@csv) + 1;
    SET @val = SUBSTRING(@csv, 1, @pos - 1);
    PRINT @val;
    SET @csv = SUBSTRING(@csv, @pos + 1, LEN(@csv));
END

效能與最佳實務建議

可用索引 (SARGable)：WHERE CHARINDEX('abc', Col) > 0 無法使用索引 (掃描)。改用 WHERE Col LIKE '%abc%' 同樣掃描；若頻繁搜尋，考慮全文檢索或計算欄位 + 索引。
大字串搜尋：CHARINDEX 對 varchar(max) 效能良好；PATINDEX 較慢，複雜模式更明顯。
定序影響：CHARINDEX('A', 'a') 在 CI (不區分大小寫) 定序下回傳 1，CS 下回傳 0。必要時指定 COLLATE Latin1_General_CS_AS。
避免迴圈：大量分割字串請用 STRING_SPLIT (SQL 2016+)、OPENJSON (SQL 2016+) 或 CLR 函數，而非 T-SQL 迴圈。

快速參考卡


-- 精確搜尋 → CHARINDEX
-- 模式搜尋 (萬用字元) → PATINDEX
-- 取代/插入 → STUFF + CHARINDEX/PATINDEX
-- 分割 → STRING_SPLIT / OPENJSON / XML
-- 全文搜尋 → CONTAINS / FREETEXT (需全文索引)

熟練掌握 CHARINDEX 與 PATINDEX 的特性邊界，能以最少程式碼解決多數字串剖析需求。