将 varchar 转换为数据类型 numeric 时出现算术溢出错误

Question

首先，我要声明，我读过各种类似的帖子，但无法识别其他发帖者遇到的此错误消息问题与我遇到的情况之间的相似之处。也许我没有正确搜索，但情况如下。我试图在表中搜索转换为数值后小于 70 的值。有时该值可以用逗号存储（即 3,080 等），因此我有一个替换语句来删除逗号。下面的查询中的 obsValue 列是 varchar(2000) ，我猜这可能与它有关。我的初始查询有效：

Select name, obsValue
From database.dbo.table
Where name in ('LDL') 
and isnumeric(obsvalue) = 1 
and cast(replace(obsvalue,',','') as decimal(18)) < 70

这会返回预期值，但它不是我尝试搜索的唯一名称。其他示例包括('LDL(CALC)')。使用 UNION 语句将允许我将查询合并在一起，但不幸的是我无法控制应用程序代码，这不是一个选项。我唯一可用的选项是使用 IN 子句，因此当我搜索各种 name 值时，最终查询将如下所示：

Select name, obsValue
From database.dbo.table
Where name in ('LDL', 'LDL(CALC)') 
and isnumeric(obsvalue) = 1 
and cast(replace(obsvalue,',','') as decimal(18)) < 70

不幸的是这样做这是我收到错误消息的地方。如果这个问题已经在其他地方得到了回答，我深表歉意。请链接，我将在信用到期时给予信用。

Answer 1

您可以将 IsNumeric(obsvalue) 替换为 (select obsvalue where isnumeric(obsvalue) = 1)。

Select name, obsValue
From database.dbo.table
Where name in ('LDL', 'LDL(CALC)') 
and isnumeric(obsvalue) = 1 
and cast(replace((select obsvalue where isnumeric(obsvalue) = 1),',','') as decimal(18)) < 70

Answer 2

obsvalue 中可能有一些值对于您的强制转换（作为十进制）函数来说太大，但您无论如何都不关心，因为它们不符合您的 in() 标准。

尝试在子查询中应用cast()，将查询限制为实际需要转换的观测值。

此外，由于逗号仅在值大于 999 时才存在，并且您正在测试小于 70 的值，因此不需要替换。事实上，您可以排除任何包含逗号的行，因为您知道它太高了。

Answer 3

问题在于，当 IN 子句中有多个项目时，SQL Server 会以不同的方式优化查询。您可以使用 CASE 语句来阻止优化

SELECT name, obsvalue
FROM database.dbo.table 
WHERE (CASE WHEN ( isnumeric(obsvalue) = 1 
             AND name in ('LDL', 'LDL(CALC)')) 
       THEN cast(replace(obsvalue,',','') as decimal(18))
       ELSE null END) < 70