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