資料清洗常需從混雜字串中提取純數字、英文或中文。本文提供三個純 T-SQL 純量函數 dbo.GetNumbers、dbo.GetLetters、dbo.GetChinese,利用 PATINDEX + STUFF 迴圈剔除非目標字元,並附測試案例。
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
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
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'%[吖-座]%';
吖-座 基於 GB2312/GBK,Unicode 環境下可能遺漏擴展字 (CJK Unified Ideographs Extension A/B)。TRANSLATE + REPLACE 組合。Regex.Replace (需 UNSAFE)。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 可內聯進查詢計畫,避免函數調用開銷,效能提升數十倍。