sqlsql-server-2008comparisonarraysvarbinary

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 behavior 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.

Solution

  • 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