SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Convert a binary float to FLOAT datatype
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 04/09/2007 :  20:11:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 04/09/2007 20:12:54

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 04/10/2007 :  01:29:49  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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

Sweden
29910 Posts

Posted - 04/10/2007 :  01:44:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
29910 Posts

Posted - 11/21/2008 :  18:35:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000