SQL Server 全角半角轉換函數：自定義標量函數實現雙向轉換

數據清洗中常遇全角/半角混雜問題（如用戶輸入「１２３」vs「123」）。SQL Server 內建無此函數，需自定義標量函數通過 Unicode 碼位偏移實現。

完整函數代碼

CREATE FUNCTION dbo.F_Convert
(
    @Str NVARCHAR(4000),  -- 待轉換字符串
    @Flag BIT             -- 0=全角轉半角, 1=半角轉全角
)
RETURNS NVARCHAR(4000)
AS
BEGIN
    DECLARE @Pat NVARCHAR(8), @Step INT, @I INT, @Spc INT;

    IF @Flag = 0
    BEGIN
        -- 全角轉半角：匹配全角範圍 U+FF01~FF5E，偏移 -65248
        SELECT @Pat = N'%[！-～]%', @Step = -65248,
               @Str = REPLACE(@Str, N'　', N' ');  -- 全角空格 U+3000 → 半角空格
    END
    ELSE
    BEGIN
        -- 半角轉全角：匹配半角可見字符 U+0021~007E，偏移 +65248
        SELECT @Pat = N'%[!-~]%', @Step = 65248,
               @Str = REPLACE(@Str, N' ', N'　');
    END

    -- 二進制排序規則確保按碼位匹配
    SET @I = PATINDEX(@Pat COLLATE LATIN1_GENERAL_BIN, @Str);

    WHILE @I > 0
    BEGIN
        SELECT @Str = REPLACE(@Str,
                              SUBSTRING(@Str, @I, 1),
                              NCHAR(UNICODE(SUBSTRING(@Str, @I, 1)) + @Step)),
               @I = PATINDEX(@Pat COLLATE LATIN1_GENERAL_BIN, @Str);
    END

    RETURN @Str;
END;
GO

調用範例

-- 全角轉半角
SELECT dbo.F_Convert(N'ＳＱＬ　Ｓｅｒｖｅｒ　２０１９', 0);
-- 結果: SQL Server 2019

-- 半角轉全角
SELECT dbo.F_Convert(N'SQL Server 2019', 1);
-- 結果: ＳＱＬ　Ｓｅｒｖｅｒ　２０１９

-- 批量更新表字段
UPDATE dbo.UserInput SET CleanValue = dbo.F_Convert(RawValue, 0);

原理解析

效能與優化建議

• 標量函數逐行調用開銷大，百萬行更新建議：
• 方案 A：CLR 函數（C# 實現，數量級更快）。
• 方案 B：內聯表值函數 (iTVF) + CROSS APPLY 向量化處理。
• 方案 C：應用層/ETL 階段完成轉換，入庫即乾淨。

擴展：處理更多字符集

若需支援日文片假名全角（U+FF66~FF9F）、圈字符等，擴展 @Pat 模式與偏移邏輯即可，核心原理不變。