資料清洗、驗證、分類常需判斷字串是否含數字、字母或特定字元集。T-SQL 內建 PATINDEX 支援類正則模式,配合字元類別 `[]`,可高效完成此類檢查。本文提供即用型模式與效能建議。
PATINDEX ( '%pattern%', expression )
-- 回傳模式首次出現的起始位置 (1-based);找不到回傳 0。
| 需求 | 模式 | 說明 |
|---|---|---|
| 含任意英文字母 (不分大小寫) | %[A-Za-z]% | 範圍 A-Z、a-z |
| 含任意數字 | %[0-9]% | 範圍 0-9 |
| 含字母或數字 (字母數字) | %[A-Za-z0-9]% | 等價 %[A-Z0-9]% (不區分大小寫排序規則下) |
| 含特定特殊字元 | %[!@#$%]% | 括號內列舉 |
| 不含數字 (純非數字) | %[^0-9]% | ^ 為否定,但僅判斷「存在非數字字元」,非「全無數字」 |
-- 純量函數:回傳 1=含有 / 0=不含
CREATE FUNCTION dbo.HasAlpha (@s NVARCHAR(MAX)) RETURNS BIT
AS BEGIN RETURN CASE WHEN PATINDEX('%[A-Za-z]%', @s) > 0 THEN 1 ELSE 0 END; END;
GO
CREATE FUNCTION dbo.HasDigit (@s NVARCHAR(MAX)) RETURNS BIT
AS BEGIN RETURN CASE WHEN PATINDEX('%[0-9]%', @s) > 0 THEN 1 ELSE 0 END; END;
GO
CREATE FUNCTION dbo.HasAlphaNum (@s NVARCHAR(MAX)) RETURNS BIT
AS BEGIN RETURN CASE WHEN PATINDEX('%[A-Za-z0-9]%', @s) > 0 THEN 1 ELSE 0 END; END;
GO
-- 使用範例
SELECT dbo.HasAlpha('abc123'), dbo.HasDigit('abc123'), dbo.HasAlphaNum('!@#'); -- 1, 1, 0
CREATE FUNCTION dbo.StrType (@s NVARCHAR(MAX)) RETURNS TINYINT
-- 1=純數字 2=純字母 3=字母數字混合 4=含其他字元
AS BEGIN
DECLARE @hasAlpha BIT = dbo.HasAlpha(@s),
@hasDigit BIT = dbo.HasDigit(@s),
@hasOther BIT = CASE WHEN PATINDEX('%[^A-Za-z0-9]%', @s) > 0 THEN 1 ELSE 0 END;
IF @hasOther = 1 RETURN 4;
IF @hasAlpha = 1 AND @hasDigit = 1 RETURN 3;
IF @hasAlpha = 1 RETURN 2;
IF @hasDigit = 1 RETURN 1;
RETURN 0; -- 空字串
END;
GO
PATINDEX 非 SARGable,**無法利用索引**,僅適用於資料清洗、ETL、驗證等非核心查詢路徑。ALTER TABLE t ADD HasDigit AS CASE WHEN PATINDEX('%[0-9]%', col) > 0 THEN 1 ELSE 0 END PERSISTED; CREATE INDEX idx_hasdigit ON t(HasDigit);Regex.IsMatch 效能更強、語法更豐富,需啟用 CLR。TRANSLATE、REGEXP_LIKE (Azure SQL / Managed Instance) 簡化邏輯。PATINDEX + 字元類別 `[A-Za-z]`/`[0-9]` 是 T-SQL 內建、零依賴的字串模式匹配利器。封裝為純量函數或計算欄位,即可在查詢中像呼叫 ISNUMERIC 一樣自然使用。對於核心高頻路徑,請採用計算欄位索引或 CLR 正則進階方案。