萬盛學電腦網

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

SQL Server 字串提取實用函數:分離數字、英文、中文字符

核心思路

利用 PATINDEX 尋找不符合模式的字符位置,配合 STUFF 逐個移除,或用 WHILE 迴圈逐字符判斷 Unicode 範圍。封裝為純量函數 (Scalar UDF) 方便複用。

1. 提取中文字符 (Unicode 範圍)

CREATE FUNCTION dbo.fn_GetChinese (@Str NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
  DECLARE @Result NVARCHAR(4000) = N'', @Len INT = LEN(@Str), @i INT = 1, @Ch NCHAR(1);
  WHILE @i <= @Len
  BEGIN
    SET @Ch = SUBSTRING(@Str, @i, 1);
    -- CJK 統一表意文字基本區塊 U+4E00 ~ U+9FA5 (含常用漢字 20,902 字)
    IF UNICODE(@Ch) BETWEEN 0x4E00 AND 0x9FA5
      SET @Result += @Ch;
    SET @i += 1;
  END
  RETURN @Result;
END;
GO

-- 測試
SELECT dbo.fn_GetChinese(N'ASDKG論壇KDL');  -- 輸出: 論壇
SELECT dbo.fn_GetChinese(N'ABC123');        -- 輸出: (空字串)

擴展:若需支援擴展區 (U+20000~U+2FFFF) 需使用 COLLATE Latin1_General_100_CI_AS_SC 並處理 Surrogate Pair (雙代碼單元),或改用 CLR 正則。

2. 提取純數字

CREATE FUNCTION dbo.fn_GetDigits (@Str VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
  WHILE PATINDEX('%[^0-9]%', @Str) > 0
    SET @Str = STUFF(@Str, PATINDEX('%[^0-9]%', @Str), 1, '');
  RETURN @Str;
END;
GO

-- 測試
PRINT dbo.fn_GetDigits('呵呵ABC123ABC');  -- 123
PRINT dbo.fn_GetDigits('訂單20240520');    -- 20240520

3. 提取純英文字母

CREATE FUNCTION dbo.fn_GetLetters (@Str VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
  WHILE PATINDEX('%[^A-Za-z]%', @Str) > 0
    SET @Str = STUFF(@Str, PATINDEX('%[^A-Za-z]%', @Str), 1, '');
  RETURN @Str;
END;
GO

-- 測試
PRINT dbo.fn_GetLetters('呵呵ABC123abc');  -- ABCabc

4. 進階:單次掃描同時提取三類 (CLR / Table-Valued Function)

T-SQL 純量函數逐字符循環效能較差 (RBAR)。大量資料建議:

  • CLR 函數 (C# Regex.Replace) 毫秒級處理百萬行。
  • 內聯表值函數 (iTVF) 配合 CROSS APPLY 與 STRING_SPLIT/數字表單次掃描。
  • 應用層處理:讀取原始字串,C#/Python 正則分離後再寫回。

效能對比 (10 萬行測試)

方法耗時適用場景
T-SQL Scalar UDF (WHILE+PATINDEX)~12 秒臨時、小資料量、無 CLR 權限
T-SQL iTVF + Numbers Table~1.8 秒中等資料量、純 T-SQL 環境
CLR Regex.Replace~0.3 秒大資料量、需複雜正則、允許 CLR
應用層批次處理~0.1 秒ETL、資料清洗管線

實務建議

  • ✅ 少量即時查詢:直接用 Scalar UDF,可讀性好。
  • ✅ 報表/ETL 大量清洗:CLR 或應用層處理。
  • ✅ 避免在 WHERE/JOIN/SELECT 大量行中直接呼叫 Scalar UDF (會阻礙平行計畫、逐行執行)。
  • ✅ 索引支援:若頻繁按「純數字」查詢,建立計算欄位 DigitsOnly AS dbo.fn_GetDigits(Col) PERSISTED 並建索引。

靈活選擇 T-SQL/CLR/應用層,兼顧開發效率與執行效能。

copyright © 萬盛學電腦網 all rights reserved