使用 TSQL 解包二进制字符串

2021-09-10 00:00:00 tsql sql-server

美好的一天，

我有许多由带有结构的 C 应用程序创建的二进制字符串.想象一下，如果你愿意，结构看起来像这样:

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;
 }

每个字符串的长度为 4 + 8 + 20 = 32 个字节.字符串的结构如下所示:

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

IIIIDDDDDDDDSSSSSSSSSSSSSSSSSSSS

我需要在 TSQL 存储过程中解压这个字符串.字符串很简单:

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

baz = SUBSTRING(binarystring, 12, 20)

int 也是.然后通过位移转换为整数(好吧，乘以 2^4、2^8 等)

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)

但是，替身更具挑战性.我可以通过遵循 IEEE754 规范来做到这一点，我对自己这样做并不满意.

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.

有没有一个函数或者什么东西可以解包 int 并从二进制字符串中加倍?

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

谢谢，

附言我自己从来没有使用过 TSQL，所以上面的片段可能是非法的，但你明白了.我正在协助一位同事.

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.

推荐答案

没有内置函数可以将二进制转换为浮点数.但是，您可以在 T-SQL 中找到用户定义的函数来进行这种转换，或者您可以使用 BitConverter.ToDouble(byte[]) 方法编写一个 clr 函数来进行这种转换.

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.

将二进制转换为浮点数的 t-sql 函数示例可以从 thread 在 sqlteam:

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

相关文章