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
– RichardTheKiwi, Commented Apr 24, 2013 at 10:04

gbn · Accepted Answer · 2013-04-24 09:53:28Z

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, 2013 at 9:53

answered Apr 24, 2013 at 9:34

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Prahalad Gaggar Over a year ago

What do i have to do when i want to search '0x0301%'

Andrew Larsson Over a year ago

+1 This is obviously the best way to do searches on BINARY fields of fixed length.

bwperrin Over a year ago

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.

RichardTheKiwi · Accepted Answer · 2013-04-24 09:58:53Z

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.

yaniv maymon · Accepted Answer · 2020-03-31 09:28:34Z

2

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

answered Mar 31, 2020 at 9:28

yaniv maymon

3783 silver badges7 bronze badges

Collectives™ on Stack Overflow

querying binary column using like in sql server

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related