萬盛學電腦網

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

SQL Server 全形/半形字元互轉標量函數:解決使用者輸入不一致導致轉型失敗

使用者輸入常混雜全形/半形數字、字母、空白,導致 CAST('123' AS INT) 失敗。本文提供高效能標量函數 dbo.ufn_ConvertWidth 統一轉換。

問題重現

SELECT CAST('123' AS INT);   -- 123 (半形,成功)
SELECT CAST('123' AS INT); -- 錯誤:無法將 varchar '123' 轉換為 int

Unicode 編碼規律

字元類型半形範圍全形範圍差值
可列印字元U+0021 ~ U+007E (33~126)U+FF01 ~ U+FF5E (65281~65374)65248
空白U+0020 (32)U+3000 (12288)12256

除空白外,全/半形按 Unicode 碼位順序一一對應,可用定值加減互轉。

通用轉換函數

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

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

    -- 使用二進位排序規則確保按 Unicode 碼位比較
    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.ufn_ConvertWidth('abc123 ABC', 1);  -- abc123 ABC

-- 全形→半形
SELECT dbo.ufn_ConvertWidth('abc123 ABC', 0);  -- abc123 ABC

-- 轉半形後可正常 CAST
SELECT CAST(dbo.ufn_ConvertWidth('123', 0) AS INT);  -- 123

效能優化建議

  • 標量函數逐列執行,大量資料效能差 → 改用 **內嵌表值函數 (iTVF)** 或 **CLR 函數**
  • iTVF 版本 (SQL 2005+):
CREATE FUNCTION dbo.itvf_ConvertWidth (@str NVARCHAR(4000), @flag BIT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT Converted = CASE @flag 
    WHEN 0 THEN REPLACE(REPLACE(@str COLLATE Latin1_General_BIN, N' ', N' '), ...) -- 需展開邏輯
    ELSE ... END;  -- 實際建議用 CLR 或應用層處理大量轉換
  • 最佳實務:**應用層輸入端統一正規化** (前端/中介層轉半形),資料庫層僅做防禦性清洗
  • 擴充:支援中文標點轉換

    中文標點全/半形碼位不連續,需建立對照表或使用 TRANSLATE (SQL 2017+) / CLR 正則表達式處理。

    統一字元寬度是資料品質的基礎,函數封裝讓清洗邏輯可複用、可測試、可維護。

    copyright © 萬盛學電腦網 all rights reserved