Sql 将FLOAT转换为VARBINARY，然后再转换回FLOAT

Updated. See my recommended solution in Addendum 2 below.个

我觉得奇怪的是，SQL Server将允许您将内部浮点表示形式保存为二进制，而不允许您undo撤消该操作.(对我来说没有任何意义).它允许您与几乎所有其他类型进行双向二进制转换，包括各种日期/时间类型.唯一的例外是Text/nText，但它们是过时的指向文本的指针存储类型.

我猜您正在将它与二进制字符串中的其他数据组合在一起，并且需要能够将所有内容映射回其确切的原始值.

我可以提供的唯一替代方法是(1)使用无损转换格式-CONVERT(VARCHAR(MAX), value, 3)转换为VARCHAR，从那里转换为二进制，然后反转该过程以恢复原始浮点值，或者(2)转换为Geometry::Point的X坐标，从那里转换为二进制，并反转该过程(使用geometter.STX属性)以恢复原始浮点值.这两种方法产生的二进制表示比直接转换为二进制要长几倍.

看来你需要 Select "best bad option."

第this db<>fiddle章一个Demo

ADDENDUM:个

我发现了一种可能更好的方法，即执行从浮点数到二进制到浮点数的往返.

在判断了GEOMETRY::Point(value, 0, 0)的二进制表示之后，我发现它的二进制表示是0x00000000010C...0000000000000000，而...是浮点数的反向二进制表示.使用这些知识，可以获取原始浮点数的二进制表示，构造GEOMETRY::Point的二进制表示，转换为GEOMETRY，并提取原始浮点数值.

以下函数封装了该技术.

CREATE FUNCTION ConvertBinaryToFloat(@FloatBinary VARBINARY(MAX)) RETURNS FLOAT
AS
BEGIN
    IF (@FloatBinary IS NULL OR LEN(@FloatBinary) <> 8) RETURN NULL

    DECLARE @PointBinary VARBINARY(MAX) =
        0x00000000010c
        + CAST(REVERSE(@FloatBinary) as VARBINARY(MAX))
        + 0x0000000000000000
    DECLARE @Point GEOMETRY = @PointBinary
    RETURN @Point.STX
END

-- Float -> binary -> float (using custom function)
DECLARE @F1 FLOAT = ACOS(-1)
DECLARE @B VARBINARY(MAX) = CONVERT(VARBINARY(MAX), @F1)
DECLARE @F2 FLOAT = dbo.ConvertBinaryToFloat(@B)

SELECT @F1, @B, @F2
SELECT CONVERT(VARBINARY(MAX), @F1), CONVERT(VARBINARY(MAX), @F2) -- Confirm identical

这正是CONVERT(FLOAT, @Binary)应该做的事情，如果它受SQL Server支持的话.

见this db<>fiddle，它演示了以前的方法和这个新方法.显示了直接编码的转换和封装该转换的函数.

我再次强调，这是在深入研究未记录的表示法，但我认为这些表示法永远不会改变，因为它会 destruct 存储几何图形的所有现有数据库.

ADDENDUM2:个

我发现了另一种方法，它直接将二进制文件分解为组件，并使用它们来构建原始的浮点值. 这依赖于IEEE 754双精度值的文档表示.

(此解决方案改编自this question的答案，它对单精度(SQL Server实数)值使用了类似的计算.

CREATE FUNCTION ConvertBinaryToFloat(@Binary VARBINARY(MAX)) RETURNS FLOAT
AS
BEGIN
    -- IEEE 754 Double Precision binary to float
    -- Layout:
    --   64 bits total
    --   1 bit sign
    --   11 bits exponent (excess 1023)
    --   52 bits fractional mantissa  (with implicit leading 1) = 1.xxxxx
    -- Does not support IEEE NaN, +Inf, or -Inf values. 

    IF (@Binary IS NULL OR LEN(@Binary) <> 8) RETURN NULL
    IF (@Binary = 0x0000000000000000) RETURN 0
    IF (@Binary = 0x8000000000000000) RETURN -0e0 -- IEEE Negative 0

    DECLARE @Int64 BIGINT = CAST(@Binary AS BIGINT)
    DECLARE @One FLOAT = 1
    DECLARE @Two FLOAT = 2
    DECLARE @Mantissa FLOAT = @One + (@Int64 & 0x000FFFFFFFFFFFFF) * POWER(@Two, -52)
    DECLARE @Exponent INT = (@Int64 & 0x7ff0000000000000) / 0x0010000000000000 - 1023

    IF (@Exponent = 1024) RETURN NULL -- Unsupported special: Inf, -Inf, NaN
  
    DECLARE @Result FLOAT =  SIGN(@Int64) * @Mantissa * POWER(@Two, @Exponent)
    RETURN @Result
END

从二进制到实数(IEEE单精度)的等效转换为:

CREATE FUNCTION ConvertBinaryToReal(@Binary VARBINARY(MAX)) RETURNS REAL
AS
BEGIN
    -- IEEE 754 Single Precision binary to float
    -- Layout:
    --   32 bits total
    --   1 bit sign
    --   8 bits exponent (excess 127)
    --   23 bits fractional mantissa  (with implicit leading 1) = 1.xxxxx
    -- Does not support IEEE NaN, +Inf, or -Inf values. 

    IF (@Binary IS NULL OR LEN(@Binary) <> 4) RETURN NULL
    IF (@Binary = 0x00000000) RETURN 0
    IF (@Binary = 0x80000000) RETURN -0e0 -- IEEE Negative 0

    DECLARE @Int32 INT = CAST(@Binary AS INT)
    DECLARE @One REAL = 1
    DECLARE @Two REAL = 2
    DECLARE @Mantissa REAL = @One + (@Int32 & 0x007FFFFF) * POWER(@Two, -23)
    DECLARE @Exponent INT = (@Int32 & 0x7f800000) / 0x00800000 - 127
    DECLARE @Result REAL =  SIGN(@Int32) * @Mantissa * POWER(@Two, @Exponent)

    IF (@Exponent = 128) RETURN NULL -- Unsupported special: Inf, -Inf, NaN

    RETURN @Result
END

测试结果(双精度/ SQL Server浮点数):

测试结果(单精度/SQL Server REAL):

请看这个db<>fiddle的演示.