SQL Server 全形/半形字元智慧轉換：自訂純量函數完整實作

問題背景

使用者輸入常混雜全形（全角）與半形（半角）字元，導致數值轉換失敗（如 `CAST('１１１' AS INT)` 報錯）、比對失效。資料庫層統一轉換為半形最穩健。

Unicode 編碼規律

規律：除空格外，全形/半形對應字碼差值固定為 65248 (0xFEE0)，且排序順序一致。

自訂轉換函數 `dbo.fn_ConvertWidth`

IF OBJECT_ID(N'dbo.fn_ConvertWidth', N'FN') IS NOT NULL
    DROP FUNCTION dbo.fn_ConvertWidth;
GO

CREATE FUNCTION dbo.fn_ConvertWidth
(
    @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
        SELECT @pat = N'%[！-～]%', @step = -65248,
               @str = REPLACE(@str, N'　', N' ');  -- 全形空格→半形空格
    END
    ELSE          -- 半形 → 全形
    BEGIN
        SELECT @pat = N'%[!-~]%', @step = 65248,
               @str = REPLACE(@str, N' ', N'　');  -- 半形空格→全形空格
    END

    SET @i = PATINDEX(@pat COLLATE Latin1_General_BIN, @str);
    WHILE @i > 0
    BEGIN
        SET @str = REPLACE(@str,
                           SUBSTRING(@str, @i, 1),
                           NCHAR(UNICODE(SUBSTRING(@str, @i, 1)) + @step));
        SET @i = PATINDEX(@pat COLLATE Latin1_General_BIN, @str);
    END
    RETURN @str;
END
GO

使用範例

-- 測試數值轉換
SELECT dbo.fn_ConvertWidth(N'１２３', 0);  -- 回傳 '123'，可直接 CAST 為 INT
SELECT CAST(dbo.fn_ConvertWidth(N'４５６', 0) AS INT);  -- 成功回傳 456

-- 批次清洗資料表
UPDATE Orders
SET CustomerName = dbo.fn_ConvertWidth(CustomerName, 0),
    Address      = dbo.fn_ConvertWidth(Address, 0)
WHERE CustomerName LIKE '%[！-～]%' COLLATE Latin1_General_BIN;

效能優化建議

• 函數逐字元 `REPLACE` 適合中短字串（< 100 字元）。大量長文本建議 CLR 函數（`Regex.Replace`）或應用層預處理。
• `COLLATE Latin1_General_BIN` 確保二進位比較，避免定序導致篩選遺漏。
• 若僅需查詢比對，可直接在 `WHERE` 使用 `COLLATE` 指定寬度不敏感定序（如 `Chinese_PRC_CI_AI_WS`），但資料本身不會被修正。