在sql server中使用like查询二进制列 [英] querying binary column using like in sql server

问题描述

我使用的是 SQL Server 2008.在我的表中有一个名为 TestData 的列，类型为 binary.

I'm using SQL Server 2008. In my table I have a column called TestData of type binary.

TestData 列中的示例数据为

Sample data in TestData column are

1. 0x0001DC780C0030373156635D0C00B8840301009A0600AC
2. 0x0301DC780C0030373156385D0C006499C401009A0600AC

下面写了两个查询来获取TestData以0x0001"开头的行.但他们都没有工作.

Wrote below two queries to get the rows where TestData starts with "0x0001". But none of them are working.

SELECT * 
FROM T_TRANSACTION 
WHERE  CAST(Indicium AS nvarchar(MAX)) LIKE '0x0001%'

----No results found

SELECT * 
FROM T_TRANSACTION 
WHERE  CAST(Indicium AS nvarchar(MAX)) LIKE '0x0001%'

----Returns all the rows

请更正查询以获得预期结果

Please correct the query to get the expected results

推荐答案

不要转换它，而是把它当作一个范围(就像你处理日期时间值一样)

Don't convert it, but treat is as a range (like you would datetime values)

DECLARE @foo TABLE (TestData varbinary(100) NOT NULL);
INSERT @foo (TestData) VALUES
         (0x0001DC780C0030373156635D0C00B8840301009A0600AC),
         (0x0001AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA),
         (0x0001AFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF),
         (0x0301DC780C0030373156385D0C006499C401009A0600AC),
         (0x0301FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF),
         (0x0302000000000000000000000000000000000000000000);

SELECT *
FROM @foo
WHERE TestData >= 0x0001 AND TestData < 0x0002;

-- added more digit for clarity of what actually happens
SELECT *
FROM @foo
WHERE TestData >= 0x00010000 AND TestData < 0x00020000;

SELECT *
FROM @foo
WHERE TestData >= 0x0001AA AND TestData < 0x0001AB;

SELECT *
FROM @foo
WHERE TestData >= 0x0301 AND TestData < 0x0302;

这样做的好处是可以在 TestData 上使用索引

This has the bonus of being able to use an index on TestData

编辑，您只需指定所需数量的数字

Edit, you just specify as many digits as you need

这篇关于在sql server中使用like查询二进制列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！