使用者輸入常混雜全形/半形數字、字母、空白,導致 CAST('123' AS INT) 失敗。本文提供高效能標量函數 dbo.ufn_ConvertWidth 統一轉換。
SELECT CAST('123' AS INT); -- 123 (半形,成功)
SELECT CAST('123' AS INT); -- 錯誤:無法將 varchar '123' 轉換為 int
| 字元類型 | 半形範圍 | 全形範圍 | 差值 |
|---|---|---|---|
| 可列印字元 | 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
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 正則表達式處理。
統一字元寬度是資料品質的基礎,函數封裝讓清洗邏輯可複用、可測試、可維護。