SQL四舍五入我的小数 [英] SQL is rounding my decimal on cast

问题描述

我正在使用SQL Server2005。数据类型为 varchar 。我正在尝试转换数字，例如

I'm using SQL Server 2005. The datatype is varchar. I'm trying to convert numbers like

1250000

至

1.25

在SQL查询中并删除尾随零。我已经尝试了许多没有成功的方法-遇到了修剪不是一个函数等问题。

within the SQL query and drop the trailing zeroes. I have tried a number of things with no success - have run into 'trim is not a function', etc.

这是我当前拥有的，在每次迭代中

Here's what I currently have, in each iteration that I have attempted.

select top 30 
    var1, var2, var3, var4, 
    CONVERT(DECIMAL(6,2), (var5 / 1000000)) as 'Number' 
from 
    databasetable 
where 
    var1 = x 

select top 30 
    var1, var2, var3, var4, 
    cast((var5 / 1000000) as decimal(6,2)) as 'Number' 
from 
    databasetable 
where 
    var1 = x 

以上两个查询均四舍五入到最接近的百万，即1250000变为1.00，依此类推任何想法将不胜感激。将小数更改为数字也无济于事。

Both queries above are rounding to the nearest million, i.e. 1250000 becomes 1.00, etc. Any ideas would be much appreciated. Changing decimal to numeric did not help either.

推荐答案

@marc_s绝对正确- 停止将数字存储为字符串！

@marc_s is absolutely right - stop storing numbers as strings!

也就是说，您是整数数学的受害者。尝试：

That said, you're a victim of integer math. Try:

SELECT CONVERT(DECIMAL(10,2), (var5 / 1000000.0)) 

由于您将数字存储为字符串，因此SQL Server可能会在应用过滤器之前尝试对非数字数据执行此计算，因此您可以说：

Since you stored numbers as strings, SQL Server may try to perform this calculation with non-numeric data before applying the filter, so you can say:

SELECT CONVERT(DECIMAL(10,2), (CASE WHEN ISNUMERIC(var5) = 1 THEN var5 END / 1000000.0))

[请注意，这也不是完美的方法。]

如果这不起作用，则说明您有一些不良数据。如果以下起作用，但是产生了太多的小数位：

If this doesn't work then you've got some bad data. If the following does work but yields too many decimal places:

select 
    var1, ...,
    CONVERT(DECIMAL(10,2), var5) / 1000000 as [Number] 

然后尝试将其包装加上额外的转换：

Then try wrapping it with an extra convert:

select 
    var1, ...,
    CONVERT(DECIMAL(10,2), CONVERT(DECIMAL(10,2), var5) / 1000000) as [Number]

这就是为什么？

此外，请不要对列别名使用'单引号' ... 某些语法已弃用，并且错误地使您的列别名看起来像字符串（IMHO）。使用 [方括号] 或避免使用关键字作为别名。

Also, please don't use 'single quotes' for column aliases ... this syntax is deprecated in some forms, and it incorrectly makes your column alias look like a string (IMHO). Use [square brackets] or just avoid using keywords as aliases.

这篇关于SQL四舍五入我的小数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！