SQL 将 nvarchar 转换为十进制时，将 nvarchar 转换为数据类型 numeric 时出现算术溢出错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22833453/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Arithmetic overflow error converting nvarchar to data type numeric when converting nvarchar to decimal
提问by Nuru Salihu
I have a column in sql which is sometimes nvarchar and number in some cases. The column has a datatype nvarchar . I am just interested in the places where its numeric where none numeric i dont need it. I use case like below
我在 sql 中有一个列，在某些情况下有时是 nvarchar 和数字。该列的数据类型为 nvarchar 。我只对我不需要它的数字的地方感兴趣。我使用如下案例
(SELECT case when isnumeric(dia) =1 then dia else '' end as days from dbo.Debtor )
the above query works fine and return the record where is numeric and empty where is null. However, i want to return my records as decimal or float. I because i want to be able to filter like
上面的查询工作正常并返回记录，其中数字为空，为空。但是，我想以十进制或浮点数形式返回我的记录。我因为我希望能够过滤
SELECT .....
WHERE EXISTS(
(SELECT case when isnumeric(dia) =1
then dia else '' end as days from dbo.Debtor ) > 0
)
The above is just an example but didnt tested. Please my Problem now is when i cast my column dia to decimal i get the error "Arithmetic overflow error converting nvarchar to data type numeric".
以上只是一个例子，但没有测试。请我现在的问题是，当我将列 dia 转换为十进制时，出现错误“将 nvarchar 转换为数据类型数字的算术溢出错误”。
Below is my query .
以下是我的查询。
(SELECT case when isnumeric(dia) =1 then
cast(dia as decimal(3,1)) else 0 end as de from dbo.Debtor )
Please any help would be appreciated. Thank you
请任何帮助将不胜感激。谢谢
回答by Igor
In order to evaluate only numeric values you need to have isnumeric in where part. try something like
为了仅计算数值，您需要在 where 部分使用 isnumeric。尝试类似
SELECT ISNULL((SELECT cast(dia as decimal(3,1)) from dbo.Debtor WHERE isnumeric(dia) =1 ),0)
回答by Mr.J
For anyone of you who is encountering this error, the solution is simple. If you can see in his example, he uses this convertion
对于遇到此错误的任何人，解决方案很简单。如果你能在他的例子中看到，他使用这个转换
cast(dia as decimal(3,1))
for example, he has a data which is 100.0007his implicit conversion will result in the said error because he want three(3)numbers with one(1)decimal point only (3,1).
例如，他有一个数据，这是100.0007他的隐式转换将导致上述错误，因为他只想要three(3)带one(1)小数点的数字(3,1)。
The solution to this is to actually checking the length of the data and determine, how many digits do you actually want.
对此的解决方案是实际检查数据的长度并确定您实际需要多少位数字。
In most cases this conversion is sufficient.
在大多数情况下，这种转换就足够了。
cast(dia as decimal(18,4))
But if your data has more numbers AFTER the decimal point, then change the conversion as needed.
但如果您的数据在小数点后有更多数字，则根据需要更改转换。
cast(dia as decimal(18,5))
and so forth....
等等……
回答by Thorsten Kettner
I assume your dbms is evaluating cast(dia as decimal(3,2))in any case. To avoid that swap numeric check and cast:
我假设您的 dbmscast(dia as decimal(3,2))在任何情况下都在评估。为了避免交换数字检查和转换：
SELECT cast(case when isnumeric(dia) = 1 then dia else null end as decimal(3,2)) from dbo.Debtor
