if OBJECTPROPERTY( OBJECT_ID( 'dbo.IEEE64ToFloat_' ) , 'IsScalarFunction' ) is not null
        drop function dbo.IEEE64ToFloat_
GO
create function dbo.IEEE64ToFloat_ (
    @IEEE64 binary(8)
)
returns float
as
begin
    /********************************************************************************/
    /* Created By       : Umachandar Jayachandran (UC)                                                  */
    /* Created On       : 28 July 2002                                                  */
    /* Description      : This function can be used to convert a 8-byte IEEE 754 format */
    /*                representation of real or double-precision value into it's    */
    /*                equivalent floating point value. This cannot be done directly */
    /*                using CAST in SQL Server. This is an optimized version of the */
    /*                UDF that does not use lookup tables to check for consistency  */
    /*                or the utility bit function "BitsToFloat". This is used by the*/
    /*                    "ParseTrcShowStats" SP.                                       */
    /********************************************************************************/
    /*  Resources  :    https://umachandar.com/resources.htm                         */
    /********************************************************************************/

    -- IEEE spec:
    -- http://www.psc.edu/general/software/packages/ieee/ieee.html

    -- Count from left to right in SQL!
    declare @Byte1 binary(1), @Byte2 binary(1), @Byte3 binary(1), @Byte4 binary(1),
            @Byte5 binary(1), @Byte6 binary(1), @Byte7 binary(1), @Byte8 binary(1),
            @1Float float, @2Float float

    set @Byte1 = substring( @IEEE64, 1, 1 )
    set @Byte2 = substring( @IEEE64, 2, 1 )
    set @Byte3 = substring( @IEEE64, 3, 1 )
    set @Byte4 = substring( @IEEE64, 4, 1 )
    set @Byte5 = substring( @IEEE64, 5, 1 )
    set @Byte6 = substring( @IEEE64, 6, 1 )
    set @Byte7 = substring( @IEEE64, 7, 1 )
    set @Byte8 = substring( @IEEE64, 8, 1 )

    set @1Float = 1.
    set @2Float = 2.

    -- Double-precision float per IEEE 754 specification.
    -- S EEEEEEEEEEE FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
    -- 0 1        11 12                                                63

    -- Sign * 2^Exp * Val
    return case @IEEE64 when 0x0 then 0 else 1 end *
           case sign( cast( @Byte1 as smallint ) & 0x80 ) when 0 then 1 else -1 end *
           ( @1Float +
             ( sign( @Byte8 & power( 2, 0 ) ) * power( @2Float, -52 ) ) +
             ( sign( @Byte8 & power( 2, 1 ) ) * power( @2Float, -51 ) ) +
             ( sign( @Byte8 & power( 2, 2 ) ) * power( @2Float, -50 ) ) +
             ( sign( @Byte8 & power( 2, 3 ) ) * power( @2Float, -49 ) ) +
             ( sign( @Byte8 & power( 2, 4 ) ) * power( @2Float, -48 ) ) +
             ( sign( @Byte8 & power( 2, 5 ) ) * power( @2Float, -47 ) ) +
             ( sign( @Byte8 & power( 2, 6 ) ) * power( @2Float, -46 ) ) +
             ( sign( @Byte8 & power( 2, 7 ) ) * power( @2Float, -45 ) ) +
             ( sign( @Byte7 & power( 2, 0 ) ) * power( @2Float, -44 ) ) +
             ( sign( @Byte7 & power( 2, 1 ) ) * power( @2Float, -43 ) ) +
             ( sign( @Byte7 & power( 2, 2 ) ) * power( @2Float, -42 ) ) +
             ( sign( @Byte7 & power( 2, 3 ) ) * power( @2Float, -41 ) ) +
             ( sign( @Byte7 & power( 2, 4 ) ) * power( @2Float, -40 ) ) +
             ( sign( @Byte7 & power( 2, 5 ) ) * power( @2Float, -39 ) ) +
             ( sign( @Byte7 & power( 2, 6 ) ) * power( @2Float, -38 ) ) +
             ( sign( @Byte7 & power( 2, 7 ) ) * power( @2Float, -37 ) ) +
             ( sign( @Byte6 & power( 2, 0 ) ) * power( @2Float, -36 ) ) +
             ( sign( @Byte6 & power( 2, 1 ) ) * power( @2Float, -35 ) ) +
             ( sign( @Byte6 & power( 2, 2 ) ) * power( @2Float, -34 ) ) +
             ( sign( @Byte6 & power( 2, 3 ) ) * power( @2Float, -33 ) ) +
             ( sign( @Byte6 & power( 2, 4 ) ) * power( @2Float, -32 ) ) +
             ( sign( @Byte6 & power( 2, 5 ) ) * power( @2Float, -31 ) ) +
             ( sign( @Byte6 & power( 2, 6 ) ) * power( @2Float, -30 ) ) +
             ( sign( @Byte6 & power( 2, 7 ) ) * power( @2Float, -29 ) ) +
             ( sign( @Byte5 & power( 2, 0 ) ) * power( @2Float, -28 ) ) +
             ( sign( @Byte5 & power( 2, 1 ) ) * power( @2Float, -27 ) ) +
             ( sign( @Byte5 & power( 2, 2 ) ) * power( @2Float, -26 ) ) +
             ( sign( @Byte5 & power( 2, 3 ) ) * power( @2Float, -25 ) ) +
             ( sign( @Byte5 & power( 2, 4 ) ) * power( @2Float, -24 ) ) +
             ( sign( @Byte5 & power( 2, 5 ) ) * power( @2Float, -23 ) ) +
             ( sign( @Byte5 & power( 2, 6 ) ) * power( @2Float, -22 ) ) +
             ( sign( @Byte5 & power( 2, 7 ) ) * power( @2Float, -21 ) ) +
             ( sign( @Byte4 & power( 2, 0 ) ) * power( @2Float, -20 ) ) +
             ( sign( @Byte4 & power( 2, 1 ) ) * power( @2Float, -19 ) ) +
             ( sign( @Byte4 & power( 2, 2 ) ) * power( @2Float, -18 ) ) +
             ( sign( @Byte4 & power( 2, 3 ) ) * power( @2Float, -17 ) ) +
             ( sign( @Byte4 & power( 2, 4 ) ) * power( @2Float, -16 ) ) +
             ( sign( @Byte4 & power( 2, 5 ) ) * power( @2Float, -15 ) ) +
             ( sign( @Byte4 & power( 2, 6 ) ) * power( @2Float, -14 ) ) +
             ( sign( @Byte4 & power( 2, 7 ) ) * power( @2Float, -13 ) ) +
             ( sign( @Byte3 & power( 2, 0 ) ) * power( @2Float, -12 ) ) +
             ( sign( @Byte3 & power( 2, 1 ) ) * power( @2Float, -11 ) ) +
             ( sign( @Byte3 & power( 2, 2 ) ) * power( @2Float, -10 ) ) +
             ( sign( @Byte3 & power( 2, 3 ) ) * power( @2Float, -09 ) ) +
             ( sign( @Byte3 & power( 2, 4 ) ) * power( @2Float, -08 ) ) +
             ( sign( @Byte3 & power( 2, 5 ) ) * power( @2Float, -07 ) ) +
             ( sign( @Byte3 & power( 2, 6 ) ) * power( @2Float, -06 ) ) +
             ( sign( @Byte3 & power( 2, 7 ) ) * power( @2Float, -05 ) ) +
             ( sign( @Byte2 & power( 2, 0 ) ) * power( @2Float, -04 ) ) +
             ( sign( @Byte2 & power( 2, 1 ) ) * power( @2Float, -03 ) ) +
             ( sign( @Byte2 & power( 2, 2 ) ) * power( @2Float, -02 ) ) +
             ( sign( @Byte2 & power( 2, 3 ) ) * power( @2Float, -01 ) ) ) *
           power( @2Float,
                  ( sign( @Byte2 & power( 2, 4 ) ) * power( 2, 00 ) ) +
                  ( sign( @Byte2 & power( 2, 5 ) ) * power( 2, 01 ) ) +
                  ( sign( @Byte2 & power( 2, 6 ) ) * power( 2, 02 ) ) +
                  ( sign( @Byte2 & power( 2, 7 ) ) * power( 2, 03 ) ) +
                  ( sign( @Byte1 & power( 2, 0 ) ) * power( 2, 04 ) ) +
                  ( sign( @Byte1 & power( 2, 1 ) ) * power( 2, 05 ) ) +
                  ( sign( @Byte1 & power( 2, 2 ) ) * power( 2, 06 ) ) +
                  ( sign( @Byte1 & power( 2, 3 ) ) * power( 2, 07 ) ) +
                  ( sign( @Byte1 & power( 2, 4 ) ) * power( 2, 08 ) ) +
                  ( sign( @Byte1 & power( 2, 5 ) ) * power( 2, 09 ) ) +
                  ( sign( @Byte1 & power( 2, 6 ) ) * power( 2, 10 ) ) - 1023 )
end
go

if OBJECTPROPERTY( OBJECT_ID( 'dbo.IEEE64ToFloat' ) , 'IsScalarFunction' ) is not null
        drop function dbo.IEEE64ToFloat
GO
create function dbo.IEEE64ToFloat (
    @IEEE64 binary(8)
)
returns float
as
begin
    /********************************************************************************/
    /* Created By       : Umachandar Jayachandran (UC)                                                  */
    /* Created On       : 28 July 2002                                                  */
    /* Description      : This function can be used to convert a 4-byte IEEE 754 format */
    /*                representation of real or single-precision value into it's    */
    /*                equivalent floating point value. This cannot be done directly */
    /*                using CAST in SQL Server.                                     */
    /********************************************************************************/
    /*  Resources  :    https://umachandar.com/resources.htm                         */
    /********************************************************************************/

    -- IEEE spec:
    -- http://www.psc.edu/general/software/packages/ieee/ieee.html

    -- Count from left to right in SQL!
    declare @1Float float, @2Float float,
            @Sign smallint, @SignBit int, @SignMask binary(1),
            @Exp int, @ExpBias int, @MaxExp int, @MinExp int, @ExpFrom int, @ExpTo int,
            @SigFrom int, @SigTo int, @SigBias smallint, @NumBits int,
            @Val float

    -- Check for invalid numbers in the binary format:
    if exists( select * from dbo.IEEE64_()
                where @IEEE64 between "HexFrom" and "HexTo"
                  and "NAN" = 1 ) return NULL

    -- Check for zero, negative zero to avoid calculation:
    if exists( select * from dbo.IEEE64_()
                where "RangeName" like '[-+]0'
                  and @IEEE64 between "HexFrom" and "HexTo"
                  and "NAN" = 0 ) return 0

    set @NumBits = 64
    set @1Float = 1.
    set @2Float = 2.

    -- Double-precision float per IEEE 754 specification.
    -- S EEEEEEEEEEE FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
    -- 0 1        11 12                                                63

    set @SignBit = @NumBits - 63
    set @SignMask = 0x80

    -- Exponent bits
    set @ExpFrom = @NumBits - 62
    set @ExpTo   = @NumBits - 52
    set @ExpBias = 1023
    set @MaxExp = 1023
    set @MinExp = -1022

    -- Significand bits
    set @SigFrom = @NumBits - 51
    set @SigTo = @NumBits - 0

    -- 1234567890123456789012345678901234567890123456789012345678901234
    --        1       2       3       4       5       6       7       8

    set @Sign = case sign( cast( substring( @IEEE64, @SignBit, 1 ) as smallint ) & @SignMask ) when 0 then 1 else -1 end

    set @Exp = cast( dbo.BitsToFloat( @IEEE64, @ExpFrom, @ExpTo, default ) as int ) - @ExpBias

    set @Val = @1Float + cast( dbo.BitsToFloat( @IEEE64, @SigFrom, @SigTo, 1 ) as float )

    -- Sign * 2^Exp * Val
    return @Sign * @Val * power( @2Float, @Exp )
end
go

-- select dbo.IEEE64ToFloat( 0x3EB36946F40DE1F3 )

declare @r float
set @r = rand(null)
set @r = rand() * rand() * power( 2., rand()*64*case when rand() > 0.5 then 0.8 else -1 end ) + rand()
select @r as FloatVal,
       dbo.IEEE64ToFloat_(  cast( @r as binary(8) ) ) as "BinToFloatConv_ (Optimized)",
       dbo.IEEE64ToFloat(  cast( @r as binary(8) ) ) as "BinToFloatConv (Normal)",
       cast( @r as binary(8) ) as BinVal
/*
FloatVal              BinToFloatConv_ (Optimized) BinToFloatConv (Normal) BinVal
--------------------- --------------------------- ----------------------- ------------------ 
5.1543975260314623E-2       5.1543975260314623E-2   5.1543975260314623E-2 0x3FAA63F8D0190760

FloatVal              BinToFloatConv_ (Optimized) BinToFloatConv (Normal) BinVal            
--------------------- --------------------------- ----------------------- ------------------ 
   10452941.629465662          10452941.629465662      10452941.629465662 0x4163EFF9B424952C

*/
This page was last updated on May 01, 2006 04:28 PM.