Issue with querying a column with varbinary(max) on SQL

Question

Hi Team,

I have a SQL query which queries data from a table which has data stored directly with data type varbinary(max). I am having issues querying a string from this table, no conversion to other data type is needed , I just need to query a value with "Like" clause from it.

When i run the query in the below two ways one returns the data and the filtered one does not:

Select Certificate
from tbl_VMM_CertificateStore

Select Certificate
from tbl_VMM_CertificateStore
Where Certificate like '%‎74fa27dc1df7f897383e80f783848c41bc2b575f%'

Below is a screenshot for the same:

Does any one know a proper way to query data in a table with data type varbinary(max) ? I do not need these value to be converted i just need to match a string of data from it.

Answer 1

I'm stealing Viorel's demo, to show a simplification:

declare @table table ( [Certificate] varbinary(max) )

 insert @table values 
 ( 0x112233AABBCC778899 ), 
 ( 0x1122330ABBCC778899 ),
 ( 0x112233445566778899 ) 

SELECT * FROM @table WHERE charindex(0x33AABB, Certificate) > 0

That is, no need to cast to varchar(MAX). Charindex can operate on vbarbinary. And that will be quite a bit faster, since it will be a binary comparison.

Answer 2

The highlighted portion does not seem to correspond.

If the sequence to find is a text, then consider these methods:

declare @table table ( [Certificate] varbinary(max) )

insert @table values 
( 0x112233AABBCC778899 ), 
( 0x1122330ABBCC778899 ),
( 0x112233445566778899 ) 

declare @to_find varchar(max) = 'aabbcc'

-- byte-aligned search
select *
from @table 
where charindex(convert(varbinary(max), @to_find, 2), [Certificate]) > 0

-- not byte-aligned search
select *
from @table 
where convert(varchar(max), [Certificate], 2 ) like '%' + @to_find + '%'

Answer 3

Hi @Gautam R ,

Thank you so much for posting here in Microsoft Q&A.

Please avoid using LIKE in your search with data type varbinary(max) since in case the binary sequence in the search key contains any 0x25 byte, it will be translated to the % character (according to the ASCII table).

You could refer Erland's query using CHARINDEX.

Or you could also refer below if you have an older verison of SQL Server or other special cases.

SELECT * FROM @table WHERE   
CHARINDEX  
(  
    CONVERT(VARCHAR(max), 0x33AABB),  
    CONVERT(VARCHAR(max), Certificate) COLLATE Latin1_General_100_BIN2  
) > 0;  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.