SQL Server尝试将varchar转换为numeric时失败并出现错误

Question

我有一列数据，包含9位数字值，但该列被定义为varchar类型。我需要将该字段转换为数字值，以便我可以将该列的总和除以100。例如：

select CAST(field1 as numeric(9,2)) / 100 from table;

运行查询时，我遇到了以下错误：算术溢出错误将varchar转换为数字数据类型。

如果我对varchar进行双重CAST操作——> int -> numeric，那么CAST就会起作用。例如：

select CAST(CAST(field1 as int) as numeric(9,2)) / 100 from table;

单个CAST从varchar转换为numeric导致SQL错误，但双重CAST却能成功，这是否有原因？

- Dan Iverson

---

你要转换的 field1 的数值是多少？ NUMERIC(9,2) 在小数点前只有7位数字 - 因此任何大于1000万的数都会导致数字溢出... - marc_s

该字段中的大部分值为0000xxxxx，其中有3-4个前导零。在CAST之前，该字段中没有小数值。 - Dan Iverson

Answer 1

如果您的字段包含9个数字，则最大可能是999,999,999。我的初步想法是，您的转换应该是CAST (NUMERIC 11, 2)

编辑

为了保险起见，对于长度为9个字符的情况，您可以拥有从999,999,999到0.1234567的数字范围。这意味着您需要小数点前9位和小数点后7位（总共16位）。因此，您的转换应该是CAST (NUMERIC (16,7)

select CAST(field1 as numeric(16,7)) from table;

- Raj More

---

无法将字段长度扩展为(11,2)。我仍然收到算术溢出错误。 - Dan Iverson

我刚刚看到了扩展的字段值，但是使用数值类型（19,8）也没有成功。 - Dan Iverson

你能否发布导致算术溢出的值？ - Raj More

谢谢Raj，你指出了我正确的方向。我们在分析中错过了一个只有“-”的字段。将“-”进行CAST转换是导致算术溢出的原因。 - Dan Iverson

Answer 2

错误的原因是有一行将“-”作为字段值。直接转换为NUMERIC不起作用，但先转换为INT会强制将“-”字段返回为0。

- Dan Iverson

---

我们在事后发现这篇文章对我们很有帮助：http://msdn.microsoft.com/en-us/library/ms186272.aspx。我们尝试使用isNumeric()函数来验证我们的数据，但减号“-”会返回一个错误的结果。 - Dan Iverson

谢谢你提供关于“-”符号的提示。它帮助我解决了一个类似的问题。 - Blackvault