Unpacking a binary string with TSQL

Question

Good Day,

I have a number of binary strings that were created by a C app with a struct. Imagine, if you will, the struct looks like this:

 struct {
     int foo; 
     double bar; //Assume 8 bytes
     char[20] baz;
 }

Each string is 4 + 8 + 20 = 32 bytes long. The structure of the string looks something like this:

IIIIDDDDDDDDSSSSSSSSSSSSSSSSSSSS

I need to unpack this string in a TSQL stored proc. The string is easy:

baz = SUBSTRING(binarystring, 12, 20)

The int also. And then convert to an integer with bit shifting (well, multiplying by 2^4, 2^8, etc)

foo_string = SUBSTRING(binarystring, 0, 4)
foo = unpack_int(foo_string)

But, the double is a lot more challenging. I am able to do it by following the IEEE754 spec, I am not happy with doing this myself.

Is there a function or something that can unpack the int and double out of a binary string?

Thank you,

P.S. I've never used TSQL myself, so the above fragments may be illegal, but you get the notion. I'm assisting a colleague.

You're apparently using some non-standard function for unpacking the int, as I get "'unpack_int' is not a recognized built-in function name." when I try it. Your question sounds like a job for a CLR procedure to me; bit-twiddling at speed is not a job that SQL is designed or optimized for. — mwigdahl
– mwigdahl, Commented Jul 12, 2011 at 13:38
Sorry, forgot to mention, unpack_int is a stored proc that we created. I agree with your statement about the "bit-twiddling" — Nicolas
– Nicolas, Commented Jul 12, 2011 at 13:58
Yeah, I would definitely go with CLR for transforming your double (and unless your unpack_int function is particularly efficient, I might reimplement it in CLR as well). I'm not aware of any SQL intrinsics that would help you here. — mwigdahl
– mwigdahl, Commented Jul 13, 2011 at 18:26

stevehem · Accepted Answer · 2011-07-14 16:58:51Z

2

There is no built-in function to convert from binary to float. However, you can find user-defined functions in T-SQL to do this conversion, or you can write a clr function to do this conversion using the BitConverter.ToDouble(byte[]) method.

An example of a t-sql function for converting binary to float can be found from a thread at sqlteam:

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

edited Jul 14, 2011 at 16:58

answered Jul 14, 2011 at 16:02

stevehem

6713 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unpacking a binary string with TSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related