前言：為什麼需要自訂純量函數？

SQL Server 內建字串函數功能有限，面對「從混雜字串提取純數字」、「分割分隔符字串」、「去除重複字符」等常見需求，自訂純量函數 (Scalar UDF) 能封裝邏輯、提升 SQL 可讀性與複用性。本文整理 6 大高頻場景函數，附帶效能優化建議。

一、 判斷/提取中文字符

1.1 判斷是否包含中文

CREATE FUNCTION dbo.fn_HasChinese(@str NVARCHAR(MAX))
RETURNS BIT
AS
BEGIN
    RETURN CASE WHEN @str LIKE N'%[一-龥]%' THEN 1 ELSE 0 END;
END;

原理： 利用 Unicode 範圍 [一-龥] (CJK 統一表意文字基本區) 配合 LIKE 模式比對，效率遠高於逐字迴圈。

1.2 提取純中文 (保留順序)

CREATE FUNCTION dbo.fn_ExtractChinese(@str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @result NVARCHAR(MAX) = N'';
    WHILE LEN(@str) > 0
    BEGIN
        DECLARE @ch NCHAR(1) = LEFT(@str, 1);
        IF UNICODE(@ch) BETWEEN 0x4E00 AND 0x9FFF -- 基本區
            SET @result += @ch;
        SET @str = RIGHT(@str, LEN(@str) - 1);
    END
    RETURN @result;
END;

優化版 (數字表/遞歸 CTE) 避免 WHILE 迴圈效能瓶頸：

CREATE FUNCTION dbo.fn_ExtractChinese_Fast(@str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN (
        SELECT STRING_AGG(ch, '') WITHIN GROUP (ORDER BY pos)
        FROM (
            SELECT SUBSTRING(@str, number, 1) AS ch, number AS pos
            FROM master..spt_values
            WHERE type = 'P' AND number BETWEEN 1 AND LEN(@str)
        ) t
        WHERE UNICODE(ch) BETWEEN 0x4E00 AND 0x9FFF
    );
END;

註：SQL Server 2017+ 可用 STRING_AGG，舊版用 FOR XML PATH 模擬。

二、 提取純數字 / 純英文

2.1 提取數字 (保留順序)

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

2.2 提取英文字母 (不區分大小寫)

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

效能警示： PATINDEX + STUFF 迴圈在長字串 (＞1000 字元) 上極慢。大數據量場景請改用 CLR 函數 或 TRANSLATE (SQL 2017+) 配合數字表集合運算。

三、 字串去重 (依分隔符 / 字符級)

3.1 依分隔符去重 (保留首次出現順序)

CREATE FUNCTION dbo.fn_DistinctByDelimiter(@str NVARCHAR(MAX), @delim NCHAR(1))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @result NVARCHAR(MAX) = N'';
    DECLARE @pos INT, @token NVARCHAR(MAX);
    DECLARE @seen TABLE (val NVARCHAR(MAX) PRIMARY KEY); -- 記憶體表去重

    WHILE LEN(@str) > 0
    BEGIN
        SET @pos = CHARINDEX(@delim, @str);
        IF @pos = 0 SET @pos = LEN(@str) + 1;
        SET @token = LEFT(@str, @pos - 1);
        SET @str = RIGHT(@str, LEN(@str) - @pos);

        IF @token <> N'' AND NOT EXISTS (SELECT 1 FROM @seen WHERE val = @token)
        BEGIN
            INSERT @seen VALUES (@token);
            SET @result = @result + CASE WHEN @result = N'' THEN N'' ELSE @delim END + @token;
        END
    END
    RETURN @result;
END;

3.2 字符級去重 (保留首次出現順序)

CREATE FUNCTION dbo.fn_DistinctChars(@str NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @result NVARCHAR(MAX) = N'';
    DECLARE @seen TABLE (ch NCHAR(1) PRIMARY KEY);
    DECLARE @i INT = 1, @ch NCHAR(1);
    WHILE @i <= LEN(@str)
    BEGIN
        SET @ch = SUBSTRING(@str, @i, 1);
        IF NOT EXISTS (SELECT 1 FROM @seen WHERE val = @ch)
        BEGIN
            INSERT @seen VALUES (@ch);
            SET @result += @ch;
        END
        SET @i += 1;
    END
    RETURN @result;
END;

四、 字串分割：取第 N 個元素

CREATE FUNCTION dbo.fn_SplitGetNth(@str VARCHAR(MAX), @delim VARCHAR(10), @index INT)
RETURNS VARCHAR(MAX)
AS
BEGIN
    IF @str IS NULL OR @index < 1 RETURN NULL;

    DECLARE @start INT = 1, @pos INT, @curr INT = 1;
    WHILE @curr < @index
    BEGIN
        SET @pos = CHARINDEX(@delim, @str, @start);
        IF @pos = 0 RETURN NULL; -- 不足 N 段
        SET @start = @pos + LEN(@delim);
        SET @curr += 1;
    END
    SET @pos = CHARINDEX(@delim, @str, @start);
    IF @pos = 0 SET @pos = LEN(@str) + 1;
    RETURN SUBSTRING(@str, @start, @pos - @start);
END;

現代替代方案 (SQL Server 2016+)： 內建 STRING_SPLIT (不保證順序) 或 OPENJSON 技巧 (保證順序)：

-- 取第 3 個元素 (順序穩定)
SELECT value FROM OPENJSON('["' + REPLACE('a,b,c,d', ',', '","') + '"]')
WHERE [key] = 2; -- 0-based index

五、 綜合應用範例：清洗髒資料

-- 原始髒資料：'SKU-2023-ABC-001、SKU-2023-ABC-002、SKU-2023-ABC-001、SKU-2024-XYZ-999'
-- 需求：提取年份、去重、輸出逗號分隔清單
SELECT 
    dbo.fn_DistinctByDelimiter(
        dbo.fn_ExtractDigits(REPLACE(dirty_col, '、', ',')) -- 先統一分隔符再提取數字
    , ',') AS clean_years
FROM staging_table;

六、 效能優化與替代方案總結

場景	純 T-SQL UDF	高效能替代方案
提取數字/字母 (短字串)	可接受	CLR Regex (Regex.Replace)
提取數字/字母 (長字串/大量列)	❌ 極慢	CLR / Python/R 外部腳本 / 應用層處理
分隔符分割	適用中小量	STRING_SPLIT (2016+) / OPENJSON / CLR
去重	適用短清單	應用層 / 臨時表 + DISTINCT
中文提取	適用短字串	全文檢索 / CLR / 應用層分詞

七、 部署與維護建議

1. 命名規範： 統一前綴 fn_，Schema 歸屬 dbo 或專用 utils。
2. 權限控管： GRANT EXECUTE ON dbo.fn_* TO [AppRole]; 避免直接授予 db_owner。
3. 版本控管： 函數腳本納入 Git，部署透過 Flyway/Liquibase/Dacpac。
4. 效能基準： 上線前用 SET STATISTICS TIME, IO ON 測試典型資料量，設定閾值告警。
5. 文件化： 使用 sp_addextendedproperty 為函數添加 MS_Description，SSMS 即可查看。

八、 總結

自訂函數是 SQL Server 邏輯封裝的基石，但純量 UDF 在集合運算中常成效能殺手。本文提供的 6 類函數覆蓋高頻清洗需求，適用於「資料量中等、邏輯固定、追求 SQL 自包含」的場景。面對高效能要求，請果斷轉向 CLR、應用層處理或資料庫外部計算引擎，讓資料庫專注於它最擅長的：關聯查詢與事務處理。