sqlsql-server

Convert float to varbinary and back to float


I'm trying to convert a float to varbinary, but when I try to get it back, I can't get the correct value.

For example:

select 
    convert(varbinary(max), convert(float, '11000000000000')) -> "0x42A402462F600000"

select 
    convert(varchar(max), convert(varbinary(max), convert(float, '11000000000000'))) -> "B¤F/`"

What am I doing wrong?

How can I get my float after it's been converted to binary?

The why of this question? Just to know how sql works and how manage correctly conversions data. Now, thanks @TN, i know how do it.


Solution

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

    I find it strange that SQL Server will let you save the internal float representation off as binary and will not let you reverse that operation. (Makes no sense to me). It lets you do bidirectional conversions to/from binary with nearly every other type, including the various date/time types. The only other exceptions are text/ntext, but those are obsolete pointer-to-text-storage types.

    I'm guessing that you are combining this with other data in a binary string and need to be able to map everything back to their exact original values.

    The only alternatives I can offer are (1) Convert to VARCHAR using the lossless conversion format - CONVERT(VARCHAR(MAX), value, 3), convert from there to binary, and reverse the process to restore the original float value, or (2) Convert to the X coordinate of a Geometry::Point, from there to binary, and reverse that process (using the geometry .STX property) to restore the original float value. Both options produce binary representations several times longer than a direct conversion to binary.

    It seems like you will need to choose the "best bad option."

    See this db<>fiddle for a demo.

    ADDENDUM:

    I found a potentially better, but undocumented way of performing a float to binary to float roundtrip.

    After examining the binary representation of GEOMETRY::Point(value, 0, 0), I found that its binary representation was 0x00000000010C...0000000000000000 where the ... is the reversed binary representation of the float. Using this knowledge, it is possible to take a binary representation of the original float, construct a binary representation of a GEOMETRY::Point, convert to GEOMETRY, and extract the original float value.

    The following function encapsulated this technique.

    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
    

    This does precisely what CONVERT(FLOAT, @Binary) should do, if it were supported by SQL Server.

    See this db<>fiddle that demonstrates the prior methods and this new method. Both a directly coded conversion and a function that encapsulates that conversion are shown.

    Again, I stress that this is diving into undocumented representations, but I do not believe these representations would ever change because it would break all existing databases that store geometry.

    ADDENDUM2:

    I found yet another method that directly deconstructs the binary into components and uses them to build up the original float value. This relies on the documented representation for IEEE 754 Double Precision values.

    (This solution was adapted from an answer to this question, which used a similar calculation for single precision (SQL Server real) values.)

    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 DATALENGTH(@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
      
        RETURN SIGN(@Int64) * @Mantissa * POWER(@Two, @Exponent)
    END
    

    The equivalent conversion from Binary to Real (IEEE Single Precision) is:

    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 DATALENGTH(@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
    
        IF (@Exponent = 128) RETURN NULL -- Unsupported special: Inf, -Inf, NaN
    
        RETURN SIGN(@Int32) * @Mantissa * POWER(@Two, @Exponent)
    END
    

    Test results (double precision / SQL Server float):

    Value Binary Result ResultBinary Compare CompareBinary
    0 0x0000000000000000 0 0x0000000000000000 Equal Identical
    0.5 0x3FE0000000000000 0.5 0x3FE0000000000000 Equal Identical
    1 0x3FF0000000000000 1 0x3FF0000000000000 Equal Identical
    11000000000000 0x42A402462F600000 11000000000000 0x42A402462F600000 Equal Identical
    0.333333333333333 0x3FD5555555555555 0.333333333333333 0x3FD5555555555555 Equal Identical
    142857142857143 0x42E03DB0A81DB6DB 142857142857143 0x42E03DB0A81DB6DB Equal Identical
    1.4142135623731 0x3FF6A09E667F3BCD 1.4142135623731 0x3FF6A09E667F3BCD Equal Identical
    3.14159265358979 0x400921FB54442D18 3.14159265358979 0x400921FB54442D18 Equal Identical
    2.71828182845905 0x4005BF0A8B145769 2.71828182845905 0x4005BF0A8B145769 Equal Identical
    0 0x8000000000000000 0 0x8000000000000000 Equal Identical

    Test results (single precision / SQL Server real):

    Value Binary Result ResultBinary Compare CompareBinary
    0 0x00000000 0 0x00000000 Equal Identical
    0.5 0x3F000000 0.5 0x3F000000 Equal Identical
    1 0x3F800000 1 0x3F800000 Equal Identical
    1.1E+13 0x55201231 1.1E+13 0x55201231 Equal Identical
    0.3333333 0x3EAAAAAB 0.3333333 0x3EAAAAAB Equal Identical
    1.428571E+14 0x5701ED85 1.428571E+14 0x5701ED85 Equal Identical
    1.414214 0x3FB504F3 1.414214 0x3FB504F3 Equal Identical
    3.141593 0x40490FDB 3.141593 0x40490FDB Equal Identical
    2.718282 0x402DF854 2.718282 0x402DF854 Equal Identical
    0 0x80000000 0 0x80000000 Equal Identical

    See this db<>fiddle for a demo.