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.