萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

T-SQL 判斷字串是否包含數字與英文字母:PATINDEX 實戰技巧

前言

資料清洗、驗證、分類常需判斷字串是否含數字、字母或特定字元集。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);
    • **應用層預處理** 寫入時打標籤。
    • **CLR 正則** (SQL Server 2005+):Regex.IsMatch 效能更強、語法更豐富,需啟用 CLR。
  • SQL Server 2022+ 可用 TRANSLATE、REGEXP_LIKE (Azure SQL / Managed Instance) 簡化邏輯。

結語

PATINDEX + 字元類別 `[A-Za-z]`/`[0-9]` 是 T-SQL 內建、零依賴的字串模式匹配利器。封裝為純量函數或計算欄位,即可在查詢中像呼叫 ISNUMERIC 一樣自然使用。對於核心高頻路徑,請採用計算欄位索引或 CLR 正則進階方案。

copyright © 萬盛學電腦網 all rights reserved