SQL Server CHARINDEX 與 SUBSTRING 函數完整語法與實用範例

CHARINDEX 尋找子字串位置，SUBSTRING 擷取子字串，是字串處理的黃金搭檔。本文整理完整語法、參數、回傳型別、邊界行為與實戰範例，助您精準操作字串。

一、CHARINDEX：尋找子字串起始位置

語法

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

參數說明

• expressionToFind：要搜尋的字元序列 (字串型別運算式)。
• expressionToSearch：被搜尋的字串運算式 (通常為資料行)。
• start_location：開始搜尋的字元位置 (1-based)。未指定、負數或 0 視為從開頭搜尋。型別可為 bigint。

回傳型別

• 若 expressionToSearch 為 varchar(max)、nvarchar(max)、varbinary(max) → bigint。
• 否則 → int。

重要行為

• 找不到 → 回傳 0。
• 依輸入定序比較，可用 COLLATE 指定定序。
• 位置從 1 開始 (非 0-based)。

範例

SELECT CHARINDEX('A', 'BADF', 1);  -- 2
SELECT CHARINDEX('A', 'BADF', 0);  -- 2 (視同 1)
SELECT CHARINDEX('A', 'BADF', 2);  -- 2
SELECT CHARINDEX('A', 'BADF', 3);  -- 0 (位置 3 之後無 A)
SELECT CHARINDEX('G', 'BADF', 3);  -- 0 (不存在)

二、SUBSTRING：擷取指定長度子字串

語法

SUBSTRING ( expression , start , length )

參數說明

• expression：字串、二進位、text、image、資料行或運算式 (不可含彙總函數)。
• start：起始位置 (1-based)，bigint。負數或 0 視為從開頭前推。
• length：要擷取的字元/位元組數，正整數，bigint。負數報錯。

位元組 vs 字元計數

• ntext、char、varchar：以字元數指定位移。
• text、image、binary、varbinary：以位元組數指定位移。

範例

SELECT SUBSTRING('GXS', -1, 3);  -- 'G'  (從倒數第1個字元前推，實際從位置1開始取3碼，只能取到G)
SELECT SUBSTRING('GXS', -1, 2);  -- NULL (長度不足)
SELECT SUBSTRING('GXS', 0, 1);   -- NULL (start 0 視同 1，但長度1從位置1取得到G? 實測 SQL Server 回傳空字串非NULL，視版本而定)
SELECT SUBSTRING('GXS', 1, 1);   -- 'G'
SELECT SUBSTRING('GXS', 1, 2);   -- 'GX'

提示：start < 1 時，SQL Server 會從位置 1 開始計算實際擷取長度 = length + start - 1，若結果 ≤ 0 則回傳空字串 (非 NULL)。

三、組合實戰：動態切割字串

-- 取得第一個 '@' 之前的部分 (Email 使用者名稱)
DECLARE @email VARCHAR(100) = 'user.name@domain.com';
SELECT SUBSTRING(@email, 1, CHARINDEX('@', @email) - 1);  -- 'user.name'

-- 取得最後一個 '\' 之後的檔名
DECLARE @path VARCHAR(200) = 'C:\Folder\SubFolder\file.txt';
-- 反轉字串找第一個 '\'
SELECT SUBSTRING(@path, LEN(@path) - CHARINDEX('\', REVERSE(@path)) + 2, LEN(@path));  -- 'file.txt'