nvarchar类型FullPrice列转decimal遇算术溢出及SSMS超时问题求助

问题拆解 #

1. 算术溢出错误：你用DECIMAL(6,2)转换时，若原字符串对应的数值超过9999.99（DECIMAL(6,2)的整数部分最多支持4位）就会溢出；另外ISNUMERIC存在局限性，部分被它判定为数字的字符串实际转换会失败。
2. 设计视图超时：SSMS设计视图修改字段会锁表，数据量大时必然超时，必须用T-SQL语句批量处理。

分步解决 #

1. 彻底清洗数据

先清除所有隐藏的非数字字符（制表符、换行符），确保字段只剩数字：

UPDATE Table1
SET FullPrice = REPLACE(REPLACE(FullPrice, CHAR(9), ''), CHAR(10), '')
WHERE FullPrice LIKE '%[' + CHAR(9) + CHAR(10) + ']%'

2. 新增decimal列（避免锁表）

不要直接修改原列，先新增一个目标类型的列，选择DECIMAL(8,2)可支持更大数值范围（比如999999.99），完全适配你需要的1813.78这类格式：

ALTER TABLE Table1
ADD FullPrice_Decimal DECIMAL(8,2) NULL;

3. 安全转换数据

用TRY_CAST替代ISNUMERIC（更可靠），先将字符串转为BIGINT避免int溢出，再除以100得到两位小数：

UPDATE Table1
SET FullPrice_Decimal = CASE
    WHEN TRY_CAST(REPLACE(FullPrice, ' ', '') AS BIGINT) IS NOT NULL
    THEN TRY_CAST(REPLACE(FullPrice, ' ', '') AS BIGINT) / 100.0
    ELSE NULL -- 无法转换的标记为NULL，后续单独处理
END

如果表数据量极大，用分批次更新避免长时间锁表：

DECLARE @BatchSize INT = 1000;
DECLARE @RowsUpdated INT = 1;

WHILE @RowsUpdated > 0
BEGIN
    UPDATE TOP(@BatchSize) Table1
    SET FullPrice_Decimal = CASE
        WHEN TRY_CAST(REPLACE(FullPrice, ' ', '') AS BIGINT) IS NOT NULL
        THEN TRY_CAST(REPLACE(FullPrice, ' ', '') AS BIGINT) / 100.0
        ELSE NULL
    END
    WHERE FullPrice_Decimal IS NULL;

    SET @RowsUpdated = @@ROWCOUNT;
END

4. 验证并替换原列

先检查转换结果，确认无误后再替换原列（操作前务必备份数据）：

-- 查看无法转换的异常数据
SELECT FullPrice, FullPrice_Decimal
FROM Table1
WHERE FullPrice_Decimal IS NULL;

-- 替换原列
BEGIN TRANSACTION
ALTER TABLE Table1 DROP COLUMN FullPrice;
EXEC sp_rename 'Table1.FullPrice_Decimal', 'FullPrice', 'COLUMN';
COMMIT TRANSACTION

内容的提问来源于stack exchange，提问作者Singh