萬盛學電腦網

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

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

資料清洗常需從混雜字串中提取純數字、英文或中文。本文提供三個純 T-SQL 純量函數 dbo.GetNumbers、dbo.GetLetters、dbo.GetChinese,利用 PATINDEX + STUFF 迴圈剔除非目標字元,並附測試案例。

1. 擷取純數字

IF OBJECT_ID('dbo.GetNumbers') IS NOT NULL DROP FUNCTION dbo.GetNumbers;
GO
CREATE FUNCTION dbo.GetNumbers(@s VARCHAR(8000))
RETURNS VARCHAR(8000)
AS BEGIN
    WHILE PATINDEX('%[^0-9]%', @s) > 0
        SET @s = STUFF(@s, PATINDEX('%[^0-9]%', @s), 1, '');
    RETURN @s;
END;
GO
-- 測試
SELECT dbo.GetNumbers('呵呵ABC123ABC');  -- 123

2. 擷取純英文字母 (不分大小寫)

IF OBJECT_ID('dbo.GetLetters') IS NOT NULL DROP FUNCTION dbo.GetLetters;
GO
CREATE FUNCTION dbo.GetLetters(@s VARCHAR(8000))
RETURNS VARCHAR(8000)
AS BEGIN
    WHILE PATINDEX('%[^a-z]%', @s) > 0
        SET @s = STUFF(@s, PATINDEX('%[^a-z]%', @s), 1, '');
    RETURN @s;
END;
GO
-- 測試
SELECT dbo.GetLetters('呵呵ABC123ABC');  -- ABCABC

3. 擷取中文字符 (GB2312/GBK 編碼範圍)

IF OBJECT_ID('dbo.GetChinese') IS NOT NULL DROP FUNCTION dbo.GetChinese;
GO
CREATE FUNCTION dbo.GetChinese(@s NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS BEGIN
    WHILE PATINDEX('%[^吖-座]%', @s) > 0
        SET @s = STUFF(@s, PATINDEX('%[^吖-座]%', @s), 1, N'');
    RETURN @s;
END;
GO
-- 測試
SELECT dbo.GetChinese(N'呵呵ABC123ABC');  -- 呵呵
-- 判斷是否含中文
SELECT * FROM (SELECT N'ASDKG論壇K聯通DL' AS col) t WHERE col LIKE N'%[吖-座]%';

效能與限制

  • RBAR (Row-By-Agonizing-Row):逐字元迴圈,**極慢**,百萬列不可用。僅適用臨時清洗、小資料量。
  • 編碼相依:中文範圍 吖-座 基於 GB2312/GBK,Unicode 環境下可能遺漏擴展字 (CJK Unified Ideographs Extension A/B)。
  • 現代替代:
    • SQL 2016+ TRANSLATE + REPLACE 組合。
    • CLR 正則表達式 Regex.Replace (需 UNSAFE)。
    • 應用層 (C#/Python) 批次處理後再寫入。

優化版:內聯表值函數 (iTVF) 避免純量函數逐列調用開銷

CREATE FUNCTION dbo.itvf_GetNumbers(@s VARCHAR(8000))
RETURNS TABLE WITH SCHEMABINDING AS RETURN
WITH nums AS (
    SELECT number FROM master..spt_values WHERE type='P' AND number BETWEEN 1 AND LEN(@s)
)
SELECT STRING_AGG(SUBSTRING(@s, number, 1), '') WITHIN GROUP (ORDER BY number) AS OnlyNumbers
FROM nums
WHERE SUBSTRING(@s, number, 1) LIKE '[0-9]';
-- 需 SQL 2017+ STRING_AGG,舊版用 FOR XML PATH('') 模擬

iTVF 可內聯進查詢計畫,避免函數調用開銷,效能提升數十倍。

copyright © 萬盛學電腦網 all rights reserved