6

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.

Naresh Goradara
  • 1,896
  • 2
  • 30
  • 41
  • 3
    In addition to gbn's answer, you should be aware that casting to `VARBINARY` without a length specifier [limits the length to 30](http://msdn.microsoft.com/en-us/library/ms188362.aspx). – Damien_The_Unbeliever Jul 11 '11 at 10:44

2 Answers2

10

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

mbeckish
  • 10,485
  • 5
  • 30
  • 55
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 2
    Example to what gbn says SELECT '4283',N'4283',CAST('4283' AS VARBINARY),CAST(N'4283' AS VARBINARY) – niktrs Jul 11 '11 at 10:14
3

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%'
Michael Riley - AKA Gunny
  • 5,074
  • 4
  • 42
  • 89
  • 1
    Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – Dima Kozhevin Jul 27 '20 at 20:02
  • The third parameter in [CONVERT](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15) should only be uaed for when converting date or time. – Luuk Jul 28 '20 at 06:27
  • @Luuk that is not correct please see the Microsoft documentation. https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15 – Michael Riley - AKA Gunny Jul 28 '20 at 17:58
  • @MichaelRiley-AKAGunny ok i should have scrolled way down.... you are right! – Luuk Jul 28 '20 at 18:29
  • 1
    @Luuk I came across this yesterday. Every day I update the company's Active Directory listings in a SQL table. Not all the fields just certain ones. The objectGUID is saved as a varbinary. I tried searching this field using like and it failed to produce results. In my search for an easy to use query I came across an example using convert with style = 2. It works like a champ. – Michael Riley - AKA Gunny Jul 28 '20 at 18:50