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

Your two queries are exactly the same? Also I added an answer with more explanation and code to do a two-sided wildcard LIKE. — RichardTheKiwi, Apr 24 '13 at 10:04

gbn · Accepted Answer · 2013-04-24T09:53:28.997

16

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

edited Apr 24 '13 at 09:53

answered Apr 24 '13 at 09:34

gbn

422,506
82
585
676

What do i have to do when i want to search **`'0x0301%'`** – Prahalad Gaggar Apr 24 '13 at 09:48
+1 This is obviously the best way to do searches on `BINARY` fields of fixed length. – Andrew Larsson May 20 '14 at 16:47
AWESOME!!! This gives me like a 1000x perf improvement. The cast(... as varchar(max)) answer below does not use my index; this answer is the way to go. "where a.hierarchy between b.hierarchy and b.hierarchy+0xFF", for example. – bwperrin Mar 31 '22 at 16:28

score 3 · Answer 2 · answered Apr 24 '13 at 09:58

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.

score 2 · Answer 3 · answered Mar 31 '20 at 09:28

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

querying binary column using like in sql server

3 Answers3

Linked