How to compare varbinary in where clause in SQL Server

Question

I want to compare varbinary type with byte array. I have tried so far:

DECLARE @data AS NVARCHAR(MAX)='4283'

Select * from table1 Where bindayData=CAST(@data AS VARBINARY)

But this does not work.

I note one strange behaviour of this: when I statically use it like

Select * from table1 Where bindayData=CAST('4283' AS VARBINARY)

then it works fine. But when I declare a variable, it doesn't work.

Please share your ideas.

Thanks, Naresh Goradara

Answer 1

Try

DECLARE @data AS NVARCHAR(MAX)='4283'

The string constant '4283' is non-unicode in the CAST, one byte per character.
This gives 4 bytes varbinary 0x34323833

When you use NVARCHAR(MAX), then it changed to unicode N'4283'string with 2 bytes per character.
This gives 8 bytes varbinary, something like 0x0034003200380033