# question

## 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

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
@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)
0 Likes 0 ·
·
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.
0 Likes 0 ·
·
0 Likes 0 ·

·
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
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
yes, I was able to find that link, but not get any use of it.
0 Likes 0 ·

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
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
0 Likes 0 ·
·
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
0 Likes 0 ·

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.