Search code examples
sql-serversql-server-2012varbinarymax

Varbinary not working correcly in where clause

I have this select query that gives the output "0x38F0A2D294A4AEBD1648C952D910AC51C40FE7"

SELECT HASHBYTES ('SHA1',CAST(54 AS varchar)+CAST(1 AS varchar)
+CAST('2013-07-26 16:30:49.9786317' AS varchar)+CAST(1 AS varchar))

Even knowing what SQL server does I'm not able to compare the column with the value. I'm doing this:

SELECT 'Worked!'
WHERE  HASHBYTES ('SHA1',CAST(54 AS varchar)+CAST(1 AS varchar)+
CAST('2013-07-26 16:30:49.9786317' AS varchar)+CAST(1 AS varchar))=0x38F0A2D294A4AEBD1648C952D910AC51C40FE7

This simply doesn't work. The strange part is that it works for other cases when the values are different.

Solution

  • Try this:

    declare @Hashthis nvarchar(2000)
SELECT @Hashthis=HASHBYTES ('SHA1',CAST(54 AS varchar)+CAST(1 AS varchar)
+CAST('2013-07-26 16:30:49.9786317' AS varchar)+CAST(1 AS varchar))

SELECT 'Worked' where HASHBYTES ('SHA1',CAST(54 AS varchar)+CAST(1 AS varchar)
+CAST('2013-07-26 16:30:49.9786317' AS varchar)+CAST(1 AS varchar))=@Hashthis