将数据类型 nvarchar 转换为数值时出错 - SQL Server

Question

我正在尝试对我的数据库中的一列进行平均。该列是 AMOUNT 并存储为 NVARCHAR(300),null 。

当我尝试将其转换为数值时，出现以下错误：

消息 8114，第 16 级，状态 5，第 1 行

将数据类型 NVARCHAR 转换为 NUMBER 时出错

这是我现在所拥有的。

 SELECT AVG(CAST(Reimbursement AS DECIMAL(18,2)) AS Amount
FROM Database
WHERE ISNUMERIC(Reimbursement) = 1
  AND Reimbursement IS NOT NULL

原文由 Pat Doyle 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

你会认为你的代码会起作用。但是，SQL Server 不保证 WHERE 子句在 SELECT 的转换发生之前过滤数据库。在我看来，这是一个错误。在微软看来，这是一项优化功能。

因此，您的 WHERE 不能保证正常工作。即使使用 CTE 也不能解决问题。

最好的解决方案是 TRY_CONVERT() 在 SQL Server 2012+ 中可用：

 SELECT AVG(TRY_CONVERT(DECIMAL(18,2), Reimbursement)) AS Amount
FROM Database
WHERE ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL;

在早期版本中，您可以使用 CASE 。 CASE 确实 保证了子句的顺序，所以：

 SELECT AVG(CASE WHEN ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL
                THEN CONVERT(DECIMAL(18,2), Reimbursement))
           END)
FROM Database;

因为 AVG() 忽略了 NULL 值，所以 WHERE 不是必需的，但如果您愿意，可以包含它。

最后，您可以使用计算列来简化代码：

 alter database add Reimbursement_Value as
    (CASE WHEN ISNUMERIC(Reimbursement) = 1 AND Reimbursement IS NOT NULL
          THEN CONVERT(DECIMAL(18,2), Reimbursement))
     END);

然后您可以将代码编写为：

 select avg(Reimbursement_Value)
from database
where Reimbursement_Value is not null;

原文由 Gordon Linoff 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

我猜因为它是 Nvarchar，所以你会在那里找到一些带有’$‘、’.’ 或 (,) 的值。我会像这样运行一个查询：

 SELECT Amount
FROM database
WHERE Amount LIKE '%$%' OR
      Amount LIKE '%.%' OR
      Amount LIKE '%,%'

看看你得到了什么，我猜你会得到一些返回的行，然后更新这些行并再试一次。

目前，您的查询将提取所有非数字的数字，这也是它失败的原因。而是尝试运行这个：

 SELECT AVG(CAST(Reimbursement AS DECIMAL(18,2)) AS Amount
FROM Database
--Changed ISNUMERIC() = to 0 for true so it will only pull numeric numbers.
WHERE ISNUMERIC(Reimbursement) = 0 and Reimbursement IS NOT NULL

原文由 Wes Palmer 发布，翻译遵循 CC BY-SA 3.0 许可协议