want to store varbinary value in nvarchar datatype?

Question

My table testuidpwd have:

User:

Uid Password Passwordhash
----------------------------------------    
121 wrwrwr 0x0013531FA845AEA1E840BDE787336ED3CAEDFE6E
122 etetxw 0x79BAEFD23C2F4D146F9BD2FDF6C14CA25AC5D6AA

• uid char data type
• password nvarchar(100) data type
• passwordhash nvarchar(100) data type

My problem is when I use function hashbytes('SHA1',password)

update testdb.dbo.testuidpwd 
set pwdhash = HashBytes('SHA1', password)

in the column pwdhash is the nvarchar(100) data type.

When I execute the below statement

update testdb.dbo.testuidpwd 
set pwdhash=HashBytes('SHA1', password)

I got the pwdhash column as 硽贕ڷד瘃快ﭙ寱캏엞 this kind of format

but I want to store the varbinary format......in the pwdhash column

For ex:

update testdb.dbo.testuidpwd 
set pwdhash = cast(hashbytes('SHA1', PWD) AS varbinary)

this statement also doesn't work

Answer 1

It would be simpler to store the binary value in a varbinary column.

That said, on SQL 2008 and later, the CONVERT function adds a style parameter for binary to char-type conversions (see the section "Binary Styles" of the MSDN page).

This should work (as long as you are on SQL 2008 or later):

update testdb.dbo.testuidpwd 
set pwdhash=CONVERT(NVARCHAR(100),HashBytes('SHA1', password),1)

You can set the style to 2 instead of 1 of you want to store the value without the leading 0x