TSQL:当某些条目是文本时如何将varchar转换为数字

Question

假设我有一个有两个相关列的表:一个主键和一个and。“金额”代表货币，是两件事之一:一个数字值(例如，47.50)或“未清”一词。

我想在这个数据集上使用聚合函数。当标准不返回任何带有“未清”金额的记录时，以下内容工作得很好：

select count(primary_key), sum(cast(amount as numeric)
from (table)
where (criteria)

但是，如果(条件)返回“未清”的任何记录，则会引发以下错误：

"Msg 8114，级别16，状态5，第1行将数据类型varchar转换为数字错误。

我希望我的查询是：(a)计算“计数”函数中的“未清”记录，以及(b)为了和函数的目的，将“未清”视为“零”。因此，简单地修改标准以排除未清理结束日期是行不通的。

Answer 1

尽管y表示列中唯一的非数字字符串是“unliquidated”，但我不一定相信这一点。无论如何，这应该是安全的：

select count(primary_key),
       sum(case when isnumeric(amount) = 1 then cast(amount as numeric) end)
from table
where (criteria);

当字符串是货币金额或用科学符号书写时，ISNUMERIC返回1。在这种情况下，演员会失败，但这听起来似乎不会发生在这里。

请注意，您必须将该条件放入case语句中，否则仍可能会出现错误。

在Server 2012+中，还可以使用try_convert()。

而且，我倾向于使用money而不是numeric作为目标数据类型。

Answer 2

使用NullIF

select count(primary_key), sum(cast(nullif(amount,'unliquidated') as numeric))
from (table)
where (criteria)

Answer 3

试试这个：

SELECT COUNT(primary_key), SUM(CASE ISNUMERIC(amount) WHEN 1 THEN cast(amount as numeric) END) AS Amount
FROM   (table)
WHERE  (criteria)