-- IEEE spec:
-- http://www.psc.edu/general/software/packages/ieee/ieee.html
if OBJECTPROPERTY( OBJECT_ID( 'dbo.IEEE32_' ) , 'IsTableFunction' ) is not null
        drop function dbo.IEEE32_
GO
create function dbo.IEEE32_ (
)
returns @IEEE32 table( "RangeName" nvarchar(128), "HexFrom" binary(4), "HexTo" binary(4), "NAN" bit, "Sign" as sign( cast( substring( "HexFrom", 1, 1 ) as tinyint ) & 0x80 ) )
begin
    /********************************************************************************/
    /* Created By       : Umachandar Jayachandran (UC)                                                  */
    /* Created On       : 28 July 2002                                                  */
    /* Description      : This table-valued function returns the valid ranges for real  */
    /*                or single-precision values stored in IEEE754 binary format.   */
    /*                This function is useful to verify the validity of floating    */
    /*                value in binary format before conversion.                     */
    /********************************************************************************/
    /*  Resources  :    https://umachandar.com/resources.htm                         */
    /********************************************************************************/
    -- Reference:
    -- http://babbage.cs.qc.edu/courses/cs341/IEEE-754references.html#kevin_chart
    insert into @IEEE32 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Quiet -NAN', 0xFFFFFFFF, 0xFFC00001, 1 )
    insert into @IEEE32 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Indeterminate', 0xFFC00000, 0xFFC00000, 1 )
    insert into @IEEE32 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Signaling -NaN', 0xFFBFFFFF, 0xFF800001, 1 )
    insert into @IEEE32 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( '-Infinity (Negative Overflow)', 0xFF800000, 0xFF800000, 1 )
    insert into @IEEE32 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Negative Normalized -1.m × 2(e-127)', 0xFF7FFFFF, 0x80800000, 0 )
    insert into @IEEE32 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Negative Denormalized -0.m × 2(-126)', 0xFFBFFFFF, 0xFF800001, 0 )
    insert into @IEEE32 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Negative Underflow', 0x80000000, 0x80000000, 0 )
    insert into @IEEE32 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( '-0', 0x80000000, 0x80000000, 0 )
    insert into @IEEE32 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( '+0', 0x00000000, 0x00000000, 0 )
    insert into @IEEE32 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Positive Underflow', 0x00000000, 0x00000000, 0 )
    insert into @IEEE32 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Positive Denormalized 0.m × 2(-126)', 0x00000001, 0x007FFFFF, 0 )
    insert into @IEEE32 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Positive Normalized 1.m × 2(e-127)', 0x00800000, 0x7F7FFFFF, 0 )
    insert into @IEEE32 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( '+Infinity (Positive Overflow)', 0x7F800000, 0x7F800000, 1 )
    insert into @IEEE32 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Signaling +NaN', 0x7F800001, 0x7FBFFFFF, 1 )
    insert into @IEEE32 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Quiet +NAN', 0x7FC00000, 0x7FFFFFFF, 1 )
    return
end
go

if OBJECTPROPERTY( OBJECT_ID( 'dbo.IEEE64_' ) , 'IsTableFunction' ) is not null
        drop function dbo.IEEE64_
GO
create function dbo.IEEE64_ (
)
returns @IEEE64 table( "RangeName" nvarchar(128), "HexFrom" binary(8), "HexTo" binary(8), "NAN" bit, "Sign" as sign( cast( substring( "HexFrom", 1, 1 ) as tinyint ) & 0x80 ) )
begin
    /********************************************************************************/
    /* Created By       : Umachandar Jayachandran (UC)                                                  */
    /* Created On       : 28 July 2002                                                  */
    /* Description      : This table-valued function returns the valid ranges for float */
    /*                or double-precision values stored in IEEE754 binary format.   */
    /*                This function is useful to verify the validity of floating    */
    /*                value in binary format before conversion.                     */
    /********************************************************************************/
    /*  Resources  :    https://umachandar.com/resources.htm                         */
    /********************************************************************************/
    -- Reference:
    -- http://babbage.cs.qc.edu/courses/cs341/IEEE-754references.html#kevin_chart
    insert into @IEEE64 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Quiet -NAN', 0xFFFFFFFFFFFFFFFF, 0xFFF8000000000001, 1 )
    insert into @IEEE64 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Indeterminate', 0xFFF8000000000000, 0xFFF8000000000000, 1 )
    insert into @IEEE64 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Signaling -NaN', 0xFFF7FFFFFFFFFFFF, 0xFFF0000000000001, 1 )
    insert into @IEEE64 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( '-Infinity (Negative Overflow)', 0xFFF0000000000000, 0xFFF0000000000000, 1 )
    insert into @IEEE64 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Negative Normalized -1.m × 2(e-1023)', 0xFFEFFFFFFFFFFFFF, 0x8010000000000000, 0 )
    insert into @IEEE64 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Negative Denormalized -0.m × 2(-1022)', 0x800FFFFFFFFFFFFF, 0x8000000000000001, 0 )
    insert into @IEEE64 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Negative Underflow', 0x8000000000000000, 0x8000000000000000, 0 )
    insert into @IEEE64 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( '-0', 0x8000000000000000, 0x8000000000000000, 0 )
    insert into @IEEE64 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( '+0', 0x0000000000000000, 0x0000000000000000, 0 )
    insert into @IEEE64 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Positive Underflow', 0x0000000000000000, 0x0000000000000000, 0 )
    insert into @IEEE64 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Positive Denormalized 0.m × 2(-1022)', 0x0000000000000001, 0x000FFFFFFFFFFFFF, 0 )
    insert into @IEEE64 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Positive Normalized 1.m × 2(e-1023)', 0x0010000000000000, 0x7FEFFFFFFFFFFFFF, 0 )
    insert into @IEEE64 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( '+Infinity (Positive Overflow)', 0x7FF0000000000000, 0x7FF0000000000000, 1 )
    insert into @IEEE64 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Signaling +NaN', 0x7FF0000000000001, 0x7FF7FFFFFFFFFFFF, 1 )
    insert into @IEEE64 ( "RangeName", "HexTo", "HexFrom", "NAN" ) values( 'Quiet +NAN', 0x7FF8000000000000, 0x7FFFFFFFFFFFFFFF, 1 )
    return
end
go

-- Used to determine invalid floating point represenstations among other things:
select * from IEEE32_()
select * from IEEE64_()
This page was last updated on May 01, 2006 04:28 PM.