SQL查询从varbinary字段中获取第一个整数

Question

我有一个有几列的表，其中一列是VarBinary(MAX)类型的。此列是用代码创建并写出的。我确切地知道它们中的每一个都是什么类型的列，但有没有办法将每个段从VarBinary数据中转换出来？

第一个是包含以下字符串大小的整数字符串后面的是双

因此：

10ABCDEFGHIJ etc.

有没有办法抓住"；10〃；在直接的SQL查询中？而不是把所有数据都拉到我的代码中？

JF-

Answer 1

按照你的要求进行SWAG，但可能不想要：

-- ASCII "10ABCDEFGHIJ" in hexadecimal.
declare @Sample as VarBinary(16) = 0x31304142434445464748494a;
select @Sample as Sample, Cast( @Sample as VarChar(16) ) as SampleVarChar,
Cast( SubString( @Sample, 1, 2 ) as Char(2) ) as FirstTwoDigitsString,
Cast( Cast( SubString( @Sample, 1, 2 ) as Char(2) ) as Int ) as FirstTwoDigitsInt;

基于您提供二进制数据的小数字节数的评论：

declare @Sample as VarBinary(256) =
0x6A0842696C6C79204A6F6E65732C31302F32332F3230313520383A32313A333120414D0942696C6C79204A6F6E65732C31302F32362F3230313520393A3135;
select @Sample as SampleVarBinary,
Cast( @Sample as VarChar(16) ) as SampleVarChar,
Cast( SubString( @Sample, 1, 1 ) as Char(1) ) as FirstByteChar,
ASCII( Cast( SubString( @Sample, 1, 1 ) as Char(1) ) ) as FirstByteInt,
Cast( 0x6A as Int ) as ByteValue;

---

0x6A08应该返回长度为"2154"的额外提示，答案更清楚。提取前两个字节作为16位LSB字：

declare @Sample as VarBinary(256) = 0x6A0842696C6C79204A6F6E65732C31302F32332F3230313520383A32313A333120414D0942696C6C79204A6F6E65732C31302F32362F3230313520393A3135;
select @Sample as SampleVarBinary,
Cast( @Sample as VarChar(16) ) as SampleVarChar,
Cast( SubString( @Sample, 1, 2 ) as NChar(1) ) as FirstWordNChar,
Unicode( @Sample ) as FirstWordInt,
Cast( 0x086A as Int ) as WordValue;

Answer 2

假设字节顺序相同，您可以使用CAST转换为二进制和从二进制转换为二进制。

例如

declare @buf varbinary(max) = cast(cast(10 as int) as binary(4)) +  cast('ABCDEFGHIJ' as binary(10))
select @buf, substring(@buf,1,4), cast(substring(@buf,1,4) as int)

Answer 3

我的用例是找到文件前导码，甚至这项工作(搜索.bzip文件(：

select * from DataTable where cast(Data as char(3)) = 'BZh'