Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Convert a binary float to FLOAT datatype

Author  Topic 

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-09 : 20:11:09
I can't take full credit for this. I want to share this with Jeff Moden who did the important research for this calculation here.

All I did was just adapting some old code according to the mantissa finding Jeff made and optimized it a little


Some test code
DECLARE	@SomeNumber FLOAT,
@BinFloat BINARY(8)

SELECT @SomeNumber = -185.6125,
@BinFloat = CAST(@SomeNumber AS BINARY(8))

SELECT @SomeNumber AS [Original],
CAST(@SomeNumber AS BINARY(8)) AS [Binary],
dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Converted],
@SomeNumber - dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Error]


And here is the code for the function.
CREATE FUNCTION dbo.fnBinaryFloat2Float
(
@BinaryFloat BINARY(8)
)
RETURNS FLOAT
AS
BEGIN
DECLARE @Part TINYINT,
@PartValue TINYINT,
@Mask TINYINT,
@Mantissa FLOAT,
@Exponent SMALLINT,
@Bit TINYINT,
@Ln2 FLOAT,
@BigValue BIGINT

SELECT @Part = 1,
@Mantissa = 1,
@Bit = 1,
@Ln2 = LOG(2),
@BigValue = CAST(@BinaryFloat AS BIGINT),
@Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)

WHILE @Part <= 8
BEGIN
SELECT @Part = @Part + 1,
@PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),
@Mask = CASE WHEN @Part = 2 THEN 8 ELSE 128 END

WHILE @Mask > 0
BEGIN
IF @PartValue & @Mask > 0
SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)

SELECT @Bit = @Bit + 1,
@Mask = @Mask / 2
END
END

RETURN SIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023)
END

Thanks again Jeff!


Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-10 : 01:29:49
Great function !!

What can be practical uses of it?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 01:44:28
I think the primary use would be with SSIS and importing some legacy data.
Import of files with binary data.

Anyhow it was a nice exercise.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 18:35:48
With suggestion from user dmw over at SqlServerCentral, we have managed to cut the code down to these
CREATE FUNCTION [dbo].[fnBinaryFloat2Float]
(
@BinaryFloat BINARY(8)
)
RETURNS FLOAT
AS
BEGIN
RETURN SIGN(CAST(@BinaryFloat AS BIGINT))
* (1.0 + (CAST(@BinaryFloat AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52))
* POWER(CAST(2 AS FLOAT), (CAST(@BinaryFloat AS BIGINT) & 0x7ff0000000000000) / 0x0010000000000000 - 1023)
END

CREATE FUNCTION [dbo].[fnBinaryReal2Real]
(
@BinaryFloat BINARY(4)
)
RETURNS REAL
AS
BEGIN
RETURN SIGN(CAST(@BinaryFloat AS INT))
* (1.0 + (CAST(@BinaryFloat AS INT) & 0x007FFFFF) * POWER(CAST(2 AS REAL), -23))
* POWER(CAST(2 AS REAL), (CAST(@BinaryFloat AS INT) & 0x7f800000) / 0x00800000 - 127)
END



E 1255'05.63"
N 5604'39.26"
Go to Top of Page
   

- Advertisement -