querying binary column using like in sql server

Question

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

Sample data in TestData column are

1. 0x0001DC780C0030373156635D0C00B8840301009A0600AC
2. 0x0301DC780C0030373156385D0C006499C401009A0600AC

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

Answer 1

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;

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

Edit, you just specify as many digits as you need

Answer 2

For a leading prefix LIKE comparison, gbn's answer will do. For a real LIKE equivalence of string searches, you can use LIKE as follows:
(borrowing schema and sample data from @gbn)

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

SELECT *
FROM @foo
WHERE CAST(TestData AS VARCHAR(MAX)) LIKE '%'+CAST(0xDC78 AS VARCHAR(MAX))+'%';

When you cast a binary value to VARCHAR, all it does is treat the raw bits as a string stream. It does not magically convert it into the string representation. Consider the example below:

select cast(0x41 as varchar(10));     -- Result: A
select cast(0x414263 as varchar(10)); -- Result: ABc

Because the byte 0x41 or ordinal 65 is 'A' in the standard Latin codepage.

Answer 3

Don't use the byte array as string, use it like a number.

all you need to do is:

SELECT * FROM T_TRANSACTION WHERE Indicium >= 0x0001

or if you want to get a scpecific one:

SELECT * FROM T_TRANSACTION WHERE Indicium >=0x0001DC780C0030373156635D0C00B8840301009A0600AC