How to compare varbinary in where clause in SQL Server

sql sql-server-2008 comparison bytearray varbinary
Naresh Goradara picture Naresh Goradara · Jul 11, 2011 · Viewed 28.2k times · Source

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

gbn picture gbn · Jul 11, 2011

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

Related questions

Stored Procedures Vs. Views

I have used both but what I am not clear is when I should prefer one over the other. I mean I know stored procedure can take in parameters...but really we can still perform the same thing using Views …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More