if OBJECTPROPERTY( OBJECT_ID( 'dbo.IEEE32ToReal_' ) , 'IsScalarFunction' ) is not null
        drop function dbo.IEEE32ToReal_
GO
create function dbo.IEEE32ToReal_ (
    @IEEE32 binary(4)
)
returns real
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. 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),
            @1Real real, @2Real real

    set @Byte1 = substring( @IEEE32, 1, 1 )
    set @Byte2 = substring( @IEEE32, 2, 1 )
    set @Byte3 = substring( @IEEE32, 3, 1 )
    set @Byte4 = substring( @IEEE32, 4, 1 )

    set @1Real = 1.
    set @2Real = 2.

    -- Single-precision float per IEEE 754 specification.
    -- S EEEEEEEE FFFFFFFFFFFFFFFFFFFFFFF
    -- 0 1      8 9                    31

    -- Sign * 2^Exp * Val
    return case @IEEE32 when 0x0 then 0 else 1 end *
           case sign( cast( @Byte1 as smallint ) & 0x80 ) when 0 then 1 else -1 end *
           ( @1Real +
             ( sign( @Byte4 & power( 2, 0 ) ) * power( @2Real, -23 ) ) +
             ( sign( @Byte4 & power( 2, 1 ) ) * power( @2Real, -22 ) ) +
             ( sign( @Byte4 & power( 2, 2 ) ) * power( @2Real, -21 ) ) +
             ( sign( @Byte4 & power( 2, 3 ) ) * power( @2Real, -20 ) ) +
             ( sign( @Byte4 & power( 2, 4 ) ) * power( @2Real, -19 ) ) +
             ( sign( @Byte4 & power( 2, 5 ) ) * power( @2Real, -18 ) ) +
             ( sign( @Byte4 & power( 2, 6 ) ) * power( @2Real, -17 ) ) +
             ( sign( @Byte4 & power( 2, 7 ) ) * power( @2Real, -16 ) ) +
             ( sign( @Byte3 & power( 2, 0 ) ) * power( @2Real, -15 ) ) +
             ( sign( @Byte3 & power( 2, 1 ) ) * power( @2Real, -14 ) ) +
             ( sign( @Byte3 & power( 2, 2 ) ) * power( @2Real, -13 ) ) +
             ( sign( @Byte3 & power( 2, 3 ) ) * power( @2Real, -12 ) ) +
             ( sign( @Byte3 & power( 2, 4 ) ) * power( @2Real, -11 ) ) +
             ( sign( @Byte3 & power( 2, 5 ) ) * power( @2Real, -10 ) ) +
             ( sign( @Byte3 & power( 2, 6 ) ) * power( @2Real, -09 ) ) +
             ( sign( @Byte3 & power( 2, 7 ) ) * power( @2Real, -08 ) ) +
             ( sign( @Byte2 & power( 2, 0 ) ) * power( @2Real, -07 ) ) +
             ( sign( @Byte2 & power( 2, 1 ) ) * power( @2Real, -06 ) ) +
             ( sign( @Byte2 & power( 2, 2 ) ) * power( @2Real, -05 ) ) +
             ( sign( @Byte2 & power( 2, 3 ) ) * power( @2Real, -04 ) ) +
             ( sign( @Byte2 & power( 2, 4 ) ) * power( @2Real, -03 ) ) +
             ( sign( @Byte2 & power( 2, 5 ) ) * power( @2Real, -02 ) ) +
             ( sign( @Byte2 & power( 2, 6 ) ) * power( @2Real, -01 ) ) ) *
           power( @2Real,
                  ( sign( @Byte2 & power( 2, 7 ) ) * power( 2, 0 ) ) +
                  ( sign( @Byte1 & power( 2, 0 ) ) * power( 2, 1 ) ) +
                  ( sign( @Byte1 & power( 2, 1 ) ) * power( 2, 2 ) ) +
                  ( sign( @Byte1 & power( 2, 2 ) ) * power( 2, 3 ) ) +
                  ( sign( @Byte1 & power( 2, 3 ) ) * power( 2, 4 ) ) +
                  ( sign( @Byte1 & power( 2, 4 ) ) * power( 2, 5 ) ) +
                  ( sign( @Byte1 & power( 2, 5 ) ) * power( 2, 6 ) ) +
                  ( sign( @Byte1 & power( 2, 6 ) ) * power( 2, 7 ) ) - 127 )
end
go

if OBJECTPROPERTY( OBJECT_ID( 'dbo.IEEE32ToReal' ) , 'IsScalarFunction' ) is not null
        drop function dbo.IEEE32ToReal
GO
create function dbo.IEEE32ToReal (
    @IEEE32 binary(4)
)
returns real
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 @1Real real, @2Real real,
            @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 real

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

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

    set @NumBits = 32
    set @1Real = 1.
    set @2Real = 2.

    -- Single-precision float per IEEE 754 specification.
    -- S EEEEEEEE FFFFFFFFFFFFFFFFFFFFFFF
    -- 0 1      8 9                    31

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

    -- Exponent bits
    set @ExpFrom = @NumBits - 30
    set @ExpTo   = @NumBits - 23
    set @ExpBias = 127
    set @MaxExp = 127
    set @MinExp = -126

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

    -- 12345678901234567890123456789012
    --        1       2       3       4

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

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

    set @Val = @1Real + cast( dbo.BitsToFloat( @IEEE32, @SigFrom, @SigTo, 1 ) as real )

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

-- select dbo.IEEE32ToReal( 0x3F569EE7 )

declare @r real
set @r = rand()
set @r = case when rand() - rand() > 0.3 then 0 else -1 end * rand() * cast(10*rand() as int)
select @r as ActualRealVal,
       dbo.IEEE32ToReal_(  cast( @r as binary(4) ) ) as "BinToRealConv_ (Optimized)",
       dbo.IEEE32ToReal(  cast( @r as binary(4) ) ) as "BinToRealConv (Normal)",
       cast( @r as binary(4) ) as BinVal

/*
ActualRealVal            BinToRealConv_ (Optimized) BinToRealConv (Normal)   BinVal     
------------------------ -------------------------- ------------------------ ---------- 
              -3.3080459                 -3.3080459               -3.3080459 0xC053B706

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