x

how do I convert a hex to a floating point number

How do I convert a hex to a floating point number? as in 0x43F8E354 should be 497.776

This is SQL Server 2008 R2

I've tried [http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-23-fn_replinttobitstring.htm][1], but it does not look as if fn_replinttobitstring exists in Sql Server 2008 R2.

In SSIS I can do like this:

Public Function ReadDouble(ByVal blobdata As Byte()) As Double
    ' Convert gain
    Dim MemoryStream As New System.IO.MemoryStream(blobdata)
    Dim BinaryReader As New System.IO.BinaryReader(MemoryStream)
    ReadDouble = BinaryReader.ReadDouble()
End Function

But how to do this in T-SQL?

[1]: http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-23-fn_replinttobitstring.htm
more ▼

asked Jul 13 '11 at 04:03 AM in Default

Henrik Staun Poulsen gravatar image

Henrik Staun Poulsen
579 13 14 16

@Henrik Staun Poulsen The function you have in your question does not do what you need even in SSIS because it converts the input to long integer not the floating point number. As @bopeavy already stated in the answer below, conversion to/from int (or bigint) from/to hex is already available in T-SQL. The only problem is that the statement

select convert(bigint, 0x43F8E354)

returns 1140384596 not the desired 497.77600098

Since your input is 4 bytes, 497.77600098 is not an accurate representation of the input. When you declare a variable as float then float(53) is assumed and 8 bytes of storage are needed. If your input is 4 bytes then your output cannot be a default float but is rather a single floating point number, which translates in T-SQL to float(24) and is sometimes called real. This one needs only 4 bytes of storage but the number of significant digits is only 7, so 497.77600098 does not comply with this limitation. Here is the sample showing that default definition of float needs 8 bytes of storage:

declare @f float = 0.0;
select datalength(@f);

-- the above returns 8
Please confirm that you need a conversion to float(24)
Jul 13 '11 at 07:38 AM Oleg

The number 497.76600098 is taken from another tool for which I do not have the source. But I can see that it converts the number to a FLOAT, not a REAL.

I had shown the wrong function. That should be fixed now.
Jul 14 '11 at 12:11 AM Henrik Staun Poulsen
@Henrik Staun Poulsen I added some comments to my answer related to your SSIS function. I hope they make sense.
Jul 14 '11 at 08:39 AM Oleg
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

There is a great discussion of this topic on the main SSC site which includes Jeff Moden, Peter Larsson (whom we know as @Peso on this site) and Martin Wills. Here is the link: http://www.sqlservercentral.com/Forums/Topic356756-8-1.aspx. I just tried the version which Martin Wills posted on page 3 which was in turn a recoding of Peter's code. Here is the paste from the post referenced above:

/* Hats off to Peter Larsson and Martin Wills for this one */
CREATE FUNCTION dbo.fnBinaryReal2Real
(
    @BinaryFloat BINARY(4)
)   RETURNS REAL 
AS

BEGIN    

    DECLARE  @Mantissa REAL, 
    @Exponent SMALLINT, 
    @IntValue INT, 
    @Real2 REAL        

    SELECT 
        @Real2 = CAST(2.0 AS REAL), 
        @IntValue = CAST(@BinaryFloat AS INT),            
        @Exponent = (@IntValue & 0x7f800000) / 0x00800000,            
        @Mantissa = 1.0 + (@IntValue &  0x007FFFFF) * POWER(@Real2, -23)    

    RETURN  SIGN(@IntValue) * @Mantissa * POWER(@Real2, @Exponent - 127)

END
GO

Here is the test:

declare @input binary(4) = 0x43F8E354;
select dbo.fnBinaryReal2Real(@input) result;

result
-------------
497.776

Please note that the desired number 497.77600098 is not real (in both senses) because it has way too many digits for a real (a.k.a. float(24)) number to afford. However, if the accurate binary representation 0x43F8E354 of the single floating point number 497.776 is sufficient then the function above should help.

<!-- Begin Edit

Here is the link to Peter's post where he restated both binary to real and binary to float as inline scalar functions: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81849. Scroll to the bottom of the post to see them.

End Edit -->

<!-- Begin Edit 2

I am glad that the links helped. However, I would like to comment on the function you use in SSIS. Please don't use it as is if you can because it leaks memory. Not much memory to speak of, but it still does which is never a good thing. Both MemoryStream and BinaryReader are disposable types but the code does not close and dispose any of them. Thus, the code will leak some memory and might eventually trash the server if the function is called frequently enough for a long enough period of time.

Usually the way any conversion works is consistent: it always converts as many bytes as needed. For example if you need to convert a byte array to REAL (System.Single in VB or float in C#) then only 4 bytes of the array will be read and converted. Conversion to FLOAT (System.Double in VB or double in C#) will take to read and then convert 8 bytes of the byte array input. Here is the function written in C# which you can use in place of the one you have (you can use online telerik code converter to convert it to VB from C#):

/// <summary>
/// Converts the byte array to a double precision floating point number.
/// These have a maximum number of significant digits limited to 15.
/// In T-SQL this type translates to float(53) or simply float by default.
/// </summary>
/// <param name="blob">System.Double (called double in C#) needs 8 bytes of
/// storage which means that the input should be limited to 8 bytes.</param>
/// <returns>Double precision floating point number</returns>
static double ReadDouble(byte[] blob)
{
    if (BitConverter.IsLittleEndian) Array.Reverse(blob);
    return BitConverter.ToDouble(blob, 0);
}

Similarly, if you need to convert a byte array to a single precision floating point number, you can feed the 4 bytes long byte array to a similar function like this:

/// <summary>
/// Converts the byte array to a single precision floating point number.
/// These have a maximum number of significant digits limited to 7.
/// In T-SQL this type translates to float(24) sometimes called real.
/// </summary>
/// <param name="blob">System.Single (called float in C#) needs 4 bytes of
/// storage which means that the input should be limited to 4 bytes.</param>
/// <returns>Single precision floating point number</returns>
static float ReadSingle(byte[] blob)
{
    if (BitConverter.IsLittleEndian) Array.Reverse(blob);
    return BitConverter.ToSingle(blob, 0);
}

As you can see, the code is pretty simple, and does not use any disposable types. Testing the latter using the bytes in your original question:

byte[] blob = new byte[] { 0x43, 0xF8, 0xE3, 0x54 };
float f = ReadSingle(blob);

Console.WriteLine(f);
Console.ReadLine();

will print 497.776 in the console window as expected.

End Edit 2 -->

Oleg

more ▼

answered Jul 13 '11 at 08:15 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

Hi Oleg,

That is two very useful links.

The first link to a function that solves my problem 300%. 100% to be able to decode the value, 100% for something that is fast, and 100% for beeing convertable to a view.

The second link to 26 pages of excellent code. Whoa.

You've made my day. Thank you very much.

Best regards, Henrik
Jul 14 '11 at 12:21 AM Henrik Staun Poulsen

Hi Oleg,

Bummer, so that is what has been taking our server down... Well, we've noticed a memory leak, but it was so small it did not matter (much (on a 512 GB machine)).

I'll fix that in 3 weeks time, when I'm back from summerholidays.

Again, Thank you very much for your help.

Best regards, Henrik
Jul 14 '11 at 12:10 PM Henrik Staun Poulsen
(comments are locked)
10|1200 characters needed characters left

This would be a good place for you to start: [Convert Hex to int][1].

[1]: http://stackoverflow.com/questions/703019/sql-query-convert-integer-to-hex-and-hex-to-integer
more ▼

answered Jul 13 '11 at 04:30 AM

bopeavy gravatar image

bopeavy
146 1 1 3

yes, I was able to find that link, but not get any use of it.
Jul 14 '11 at 12:06 AM Henrik Staun Poulsen
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x977
x562
x2

asked: Jul 13 '11 at 04:03 AM

Seen: 3334 times

Last Updated: Jul 14 '11 at 12:03 AM