将Float转换为十进制（SQL Server）

I need to convert a Float to Decimal(28,10) in SQL Server. My problem is that due to the nature of float, and the way conversions are done, simply casting the float may make it appear to be the wrong number to my users.

我需要在SQL Server中将Float转换为Decimal(28,10)。我的问题是,由于浮动的性质和转换的方式,简单地转换浮动可能会使我的用户看起来错误的数字。

For example:

Float:               280712929.22 
Cast as Decimal:     280712929.2200000300
What I think I want: 280712929.2200000000

I understand a bit about the way float works (that it's an approximate data type etc.), but admittedly not enough to understand why it adds the 300 at the end. Is it simply garbage as a side effect of the conversion, or is it somehow a more accurate representation of what the float actually stores? To me it looks like it's pulled precision out of thin air.

我对float的工作方式(它是一种近似的数据类型等)有一点了解,但是不能理解为什么它最后增加了300。它只是简单的垃圾作为转换的副作用,还是以某种方式更精确地表示浮动实际存储的内容?对我来说,看起来它是凭空而来的精确度。

Ultimately, I need it be accurate, but also to look "right." I think I need to get that bottom number, as then it looks like I've just added trailing zeroes. Is this possible? Is this a good or bad idea, and why? Other suggestions are welcome.

最终,我需要它是准确的,但也要看起来“正确”。我想我需要得到那个底部数字,因为它看起来我刚刚添加了尾随零。这可能吗?这是一个好主意还是坏主意,为什么?其他建议是受欢迎的。

Some other examples:

其他一些例子:

Float:           364322379.5731
Cast as Decimal: 364322379.5730999700
What I want:     364322379.5731000000

Float:           10482308902
Cast as Decimal: 10482308901.9999640000
What I want:     10482308902.0000000000

Side note: the new database table that I'm putting these values into is readable by my user. They actually only need two decimal places right now, but that might change in the future so we've decided to go with Decimal(28,10). The long term goal is to convert the float columns that I'm getting my data from to decimal as well.

旁注:我将这些值放入的新数据库表是我的用户可读的。它们实际上现在只需要两个小数位,但是将来可能会改变,所以我们决定使用Decimal(28,10)。长期目标是将我获取数据的浮点数转换为小数。

EDIT: Sometimes the floats that I have have more decimal places than I'll ever need, for example: -0.628475064730907. In this situation the cast to -0.6284750647 is just fine. I basically need my result to add zeroes on to the end of the float until I have 10 decimal places.

编辑:有时我的浮点数比我需要的更多小数位,例如:-0.628475064730907。在这种情况下,施法到-0.6284750647就好了。我基本上需要我的结果将零添加到浮点的末尾,直到我有10个小数位。

3 个解决方案

#1

You need to cast once to round, and once to add decimal places back (there are other ways too, surely, using STR, ROUND etc.):

你需要施放一次到圆形,然后再添加一次小数位(还有其他方法,当然,使用STR,ROUND等):

DECLARE @c TABLE(x FLOAT);

INSERT @c SELECT 280712929.22;
INSERT @c SELECT 364322379.5731;
INSERT @c SELECT 10482308902;

SELECT x, 
    d = CONVERT(DECIMAL(28,10), x), 
    rd = CONVERT(DECIMAL(28,10), CONVERT(DECIMAL(28,4), x))
FROM @c;

Results:

x               d                       rd
--------------  ----------------------  ----------------------
280712929.22    280712929.2200000300    280712929.2200000000
364322379.5731  364322379.5730999700    364322379.5731000000
10482308902     10482308902.0000000000  10482308902.0000000000

If you want it to be accurate and look right, stop using FLOAT, which is an approximate data type and defies logic for most people outside of a strict math background. Use DECIMAL with a larger scale than you need, and format it to the number of decimal places you need now (in your queries, or create a view, or create a computed column). If you store more info than you need now, you can always expose more later. You can also choose to not give users direct access to your table.

如果您希望它准确并且看起来正确,请停止使用FLOAT,这是一种近似数据类型,并且违反严格数学背景之外的大多数人的逻辑。使用比您需要的更大比例的DECIMAL,并将其格式化为您现在需要的小数位数(在查询中,或创建视图,或创建计算列)。如果您存储的信息超出了现在的需求,您可以随时公开更多信息。您也可以选择不让用户直接访问您的表。

#2

I'm not sure if this falls under necroposting, but I had a similar problem recently so I thought I might post.

我不确定这是否属于necroposting,但我最近遇到了类似的问题所以我想我可能会发布。

This may be ugly as sin, but seemed to work (modified from Aaron's response above).

这可能是罪恶的丑陋,但似乎有效(从亚伦的回应中修改)。

DECLARE @myTable TABLE(x FLOAT);

INSERT INTO @myTable VALUES
   (280712929.22),
   (364322379.5731),
   (10482308902),   
   (-0.628475064730907);

SELECT x, 
       d = CONVERT(DECIMAL(28,10), x),                 
       NewDec = CONVERT(DECIMAL(28,10),           
                        CONVERT(DECIMAL(16,15), 
                                LEFT(CONVERT(VARCHAR(50), x, 2),17)) 
                        * POWER(CONVERT(DECIMAL(38,19),10),  
                                RIGHT(CONVERT(varchar(50), x,2),4)))                       
FROM @myTable; 

Results:

x                   d                       NewDec
------------------  ----------------------  ----------------------
280712929.22        280712929.2200000300    280712929.2200000000
364322379.5731      364322379.5731000300    364322379.5731000000
10482308902         10482308902.0000000000  10482308902.0000000000
-0.628475064730907  -0.6284750647           -0.6284750647

#3

I came to this problem when I was needed to convert big data table from float to decimal(28,15). As specify in comment of the @Dan answer, his answer was not working as expected for some values.

当我需要将大数据表从float转换为decimal(28,15)时,我遇到了这个问题。正如在@Dan回答的评论中指出的那样,他的答案对于某些值没有按预期工作。

Here is my final version used to make the update

这是我用于进行更新的最终版本

DECLARE @myTable TABLE(x FLOAT);

INSERT INTO @myTable VALUES
(280712929.22),
(364322379.5731),
(10482308902),   
(-0.628475064730907),   
(-0.62847506473090752665448522),
(8.828),
(8.9),
(8.999),
(8),
(9),
(0.000222060864421707),
(5.43472210425371E-323),
(1.73328282953587E+81);

SELECT x, 
   d = CONVERT(DECIMAL(28,15), ROUND( CONVERT(DECIMAL(28,15),           
                CONVERT(DECIMAL(16,15), 
                        LEFT(CONVERT(VARCHAR(50), x, 2),17)) 
                * POWER(CONVERT(DECIMAL(38,19),10),  
                         CASE   
  WHEN RIGHT(CONVERT(varchar(50), x,2),4) > 12 THEN 12
  ELSE RIGHT(CONVERT(varchar(50), x,2),4)
  END)), 14 )),

 SimpleVarchar = TRY_CONVERT(VARCHAR(50), x),
 AnsiVarchar = TRY_CONVERT(VARCHAR(50), x, 2)
 FROM @myTable; 

I loosed a very small part of precision because I rounded on the decimal 14. But it's acceptable in my case.

我放弃了一小部分精度,因为我在小数点上四舍五入。但在我的情况下这是可以接受的。