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.