SQL Server：varchar转decimal报错，如何定位问题数据行？

问题根源 #

你使用的ISNUMERIC()和NOT LIKE '%[^0-9]%'存在明显局限性：

• ISNUMERIC()会将$、+、-、e等符号判定为有效数字，但这类值无法转换为decimal；
• NOT LIKE '%[^0-9]%'会过滤掉带小数点、正负号的合法数值，同时可能漏过含特殊字符的非法值。

解决方案 #

根据SQL Server版本选择对应方法：

方法1：SQL Server 2012及以上版本（高效直接）

用TRY_CAST/TRY_CONVERT直接筛选转换失败的行：

SELECT columnName
FROM tableName
WHERE TRY_CAST(columnName AS decimal(38,20)) IS NULL

百万级数据可分批查询优化性能，比如按主键范围拆分：

SELECT columnName
FROM tableName
WHERE ID BETWEEN 1 AND 100000
AND TRY_CAST(columnName AS decimal(38,20)) IS NULL

方法2：兼容老版本SQL Server（无TRY_CAST支持）

通过字符规则排查所有非法情况：

SELECT columnName
FROM tableName
WHERE 
    -- 包含多个小数点
    LEN(columnName) - LEN(REPLACE(columnName, '.', '')) > 1
    -- 正负号不在开头位置
    OR (CHARINDEX('-', columnName) > 1 OR CHARINDEX('+', columnName) > 1)
    -- 存在数字、小数点、正负号以外的字符
    OR columnName LIKE '%[^0-9.+-]%'
    -- 空值或仅含符号/小数点的无效值
    OR columnName IN ('', '.', '-', '+', '-.', '+.')
    -- 数值超出decimal(38,20)范围（整数部分超过18位）
    OR (CHARINDEX('.', columnName) = 0 AND LEN(columnName) > 18)
    OR (CHARINDEX('.', columnName) > 0 AND CHARINDEX('.', columnName) - 1 > 18)

方法3：单独排查超精度数值

若报错是因为数值超出decimal(38,20)的精度限制，可单独筛选这类行：

SELECT columnName
FROM tableName
WHERE 
    -- 无小数点时，数字长度超过18位
    (CHARINDEX('.', columnName) = 0 AND LEN(columnName) > 18)
    -- 有小数点时，整数部分长度超过18位
    OR (CHARINDEX('.', columnName) > 0 AND CHARINDEX('.', columnName) - 1 > 18)

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