問題描述

美好的一天，

我有許多由帶有結構的 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

這篇關于使用 TSQL 解包二進制字符串的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！