How to compare varbinary in where clause in SQL Server

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' A开发者_JAVA百科S VARBINARY)

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

Please share your ideas.

Thanks, Naresh Goradara

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

Using a style of 2 in the convert function does not truncate the final result. It leaves off the "0x" prefex in essence converting the result to a non-binary result. Please refer to Microsoft's documentation. There is an example at the bottom: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

Select * 
from table1 
Where convert(varchar(max),bindayData,2) like '%4283%'