如何在SQL Server中获取浮点数的精确字符表示？

Question

我们正在验证从一个SQL Server迁移到另一个SQL Server的数据。其中之一我们要验证的是一些数值数据是否正确地转移了。在新系统中，数值数据以浮点数（float）数据类型存储。

我们知道浮点数存在许多问题，不能保证精确的数字准确性，并且无法使用精确等式比较浮点数据。我们无法控制数据库模式和数据类型，这些都是不同的问题。

在这个特定案例中，我们试图验证某些比率值是否正确地传输。其中一条具体的数据验证规则是所有比率值都应该在小数点右侧没有超过4位。

例如，有效的比率看起来像：

.7542
1.5423

无效比率包括：

.12399794301
12.1209377

我们想要做的是计算小数点右侧的数字数量，并找到所有浮点值右侧有超过四个数字的情况。我们一直在使用SUBSTRING、LEN、STR和其他一些函数来实现这一点，如果我们将数字字段类型设置为十进制并将其转换为字符，则肯定会起作用。然而，当尝试将浮点数转换为字符值时，我们发现SQL Server似乎总是在两者之间转换为十进制。例如，查询SQL Server Enterprise Manager中的字段时显示此值：

1.4667

尝试使用SQL Server推荐的函数将其转换为字符串：

LTRIM(RTRIM(STR(field_name, 22, 17)))

返回此值：

1.4666999999999999

如果SQL Server直接从float转换为char（然后我们可以从中删除尾随的零），我期望的值是：

1.4667000000000000

在SQL Server中，是否有一种直接将浮点数转换为字符的方法，而不需要经过看起来是中间步骤的十进制转换？我们还尝试了CAST和CONVERT函数，并且得到了与STR函数类似的结果。涉及的SQL Server版本为：SQL Server 2012 SP2。谢谢。

- magnum_pi

---

我的猜测是，该值实际上是 1.4666999999999999。当您在企业管理器中选择它时，它会为您四舍五入。 - Tom H

假设这些数字按照IEEE 754标准以64位浮点数的形式存储，1.4667将被存储为二进制表示，该表示（精确地）对应于十进制数1.466699999999999892708046900224871933460235595703125。正确舍入到16个小数位，即为1.4666999999999999，这正是您得到的结果。也许您可以舍入到更少的小数位，这样末尾的这些小误差就会被隐藏起来？ - Thomas Padron-McCarthy

谢谢大家。我相信你们俩都是正确的，问题的根源在于a)没有一个确切的数字1.4667可以表示为浮点数，b)Enterprise Manager在显示这些数字之前实际上会四舍五入。 (https://dev59.com/Tmsz5IYBdhLWcg3wHUW0) 在SQL Server管理工具中进行舍入方面提供了更多背景资料。我们正在尝试使用链接文章中提到的其他SQL客户端（例如SQLCMD）来验证我们得到的输出。 - magnum_pi

Answer 1

您的验证规则似乎有误。

SQL Server中的FLOAT，或者FLOAT(53)，根据IEEE 754标准在内部被存储为64位浮点数，包括53位尾数（“值”）和一个指数。这53个二进制数字对应于约15个十进制位。

浮点数具有有限的精度，并不意味着它们本身是“模糊的”或不精确的，而是并非所有数字都可以精确表示，必须使用另一个数字代替。

例如，您的1.4667没有精确的表示方法，它将被存储为二进制浮点数，该浮点数（完全）对应于十进制数1.466699999999999892708046900224871933460235595703125。正确舍入到16位小数，得到1.4666999999999999，这正是您获得的结果。

由于“在SQL Server中的浮点值的确切字符表示”是1.466699999999999892708046900224871933460235595703125，因此“小数点右侧不超过4位”的验证规则显然有缺陷，至少如果您将其应用于“确切字符表示”的话。

然而，您可能能够将存储的数字四舍五入到更少的小数位数，以隐藏小数点后的小误差。将字符表示转换为15位小数而不是16位（还记得开头提到的那“15个十进制位”吗？）将给您1.466700000000000，然后您可以检查小数点后第四位之后的所有小数位是否为零。

- Thomas Padron-McCarthy

Answer 2

你可以尝试使用cast将其转换为varchar。

select case when
len(
substring(cast(col as varchar(100))
          ,charindex('.',cast(col as varchar(100)))+1
          ,len(cast(col as varchar(100)))
         )
   ) = 4
then 'true' else 'false' end
from tablename
where charindex('.',cast(col as varchar(100))) > 0

- Vamsi Prabhala

Answer 3

针对这个特定的数字，不要使用STR()函数，而是使用转换或强制转换为varchar。但是，一般来说，当存储在float类型中时，您总会遇到精度问题...这是该数据类型存储的本质。您能做的最好的事情就是将其规范化为NUMERIC类型，并与阈值范围（+/- .0001，例如）进行比较。请参见以下内容，了解不同转换方式的详细信息：

declare @float float = 1.4667
select  @float,
        convert(numeric(18,4), @float),
        convert(nvarchar(20), @float),
        convert(nvarchar(20), convert(numeric(18,4), @float)),
        str(@float, 22, 17),
        str(convert(numeric(18,4), @float)),
        convert(nvarchar(20), convert(numeric(18,4), @float))

- Chris Steele

Answer 4

不要将其转换为VarChar，您可以尝试将其转换为带有4个小数位的十进制数，并检查它是否与之前的值相同。

case when field_name <> convert(numeric(38,4), field_name) 
     then 1 
     else 0 
end

- dnoeth

---

虽然这是一个有趣的验证事项，但并不完全是我要问的问题。我们已经看到，由于浮点数在转换为十进制时的工作方式，这个检查将在相当多的情况下失败。这是一个单独的问题。对于这个问题，我只想知道如何获得 SQL Server 中浮点值的精确字符表示。 - magnum_pi

@magnum_pi：嗯，你知道FLOAT不是精确的，但是想要一个精确的表示 :) 你可以减去两个值并检查差异是否超出了边界。 - dnoeth

@dnoeth：好吧，公平地说，FLOAT值确实是精确的。只是并非所有数字都可以使用现有的精确值来精确表示。 - Thomas Padron-McCarthy

问题不是关于浮点数的精确抽象表示。问题是“为什么我无法获得字符转换输出，看起来是存储在SQL Server中的浮点值'1.4667'？”问题的关键在于我的问题假设'1.4667'是实际存储在SQL Server内部的浮点值，但事实并非如此。我没有意识到这一点，因为我没有意识到SQL Server Management Studio会对浮点数进行四舍五入显示。 - magnum_pi

Answer 5

这里的问题是浮点数是一种约略的数字数据类型，其精度约为七位数字。这意味着在使用比十进制/数值型更少的存储空间时，它会接近该值。这就是为什么您不会将浮点数用于需要精确精度的值的原因。 请查看此示例：

DECLARE @t TABLE (
col FLOAT
)

INSERT into @t (col)
VALUES (1.4666999999999999)
,(1.4667)
,(1.12399794301)
,(12.1209377);

SELECT col
, CONVERT(NVARCHAR(MAX),col) AS chr
, CAST(col as VARBINARY) AS bin
, LTRIM(RTRIM(STR(col, 22, 17))) AS rec
FROM @t

正如您所看到的，浮点数1.4666999999999999的二进制等于1.4667。根据您的需求，我认为以下查询适合：

SELECT col
, RIGHT(CONVERT(NVARCHAR(MAX),col), LEN(CONVERT(NVARCHAR(MAX),col)) - CHARINDEX('.',CONVERT(NVARCHAR(MAX),col))) AS prec
from @t

- Raul

---

一个小细节：SQL Server 中的数据类型 FLOAT 相当于 FLOAT(53)，具有 53 位尾数，并且相当于许多编程语言中所称的“double”，而不是“float”。这相当于约 15 个十进制数字，而不是 7 个。 - Thomas Padron-McCarthy