利用 PATINDEX 尋找不符合模式的字符位置,配合 STUFF 逐個移除,或用 WHILE 迴圈逐字符判斷 Unicode 範圍。封裝為純量函數 (Scalar UDF) 方便複用。
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 正則。
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
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
T-SQL 純量函數逐字符循環效能較差 (RBAR)。大量資料建議:
CROSS APPLY 與 STRING_SPLIT/數字表單次掃描。| 方法 | 耗時 | 適用場景 |
|---|---|---|
| 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、資料清洗管線 |
DigitsOnly AS dbo.fn_GetDigits(Col) PERSISTED 並建索引。靈活選擇 T-SQL/CLR/應用層,兼顧開發效率與執行效能。