How to convert Hasbytes to varchar ? I came across this question recently when I was working on my previous article. Then I started with my research to develop a solution.
Given below is the script that can convert Hashbytes to Varchar :
DECLARE @varchar varchar(Max); DECLARE @hashbytes varbinary(20) -- Convert 'raresql' string into Hasbytes SET @hashbytes=HASHBYTES('SHA1','raresql'); -- Select Hasbytes value Select @hashbytes as Hasbytes --Convert varbinary value to varchar value Set @varchar='0x' + cast('' as xml).value ('xs:hexBinary(sql:variable("@hashbytes"))', 'varchar(max)'); --Select varchar value Select @varchar as [Varchar]
Great suggestion, I am working just now, for integrating credit card payment using MAC feature.
Fabio
Thanks, this helped me a lot
Might this be an alternative without xml tweak?
by using Convert and the optional style parameter.
DECLARE @varchar varchar(Max);
DECLARE @hashbytes varbinary(20)
— Convert ‘raresql’ string into Hasbytes
SET @hashbytes=HASHBYTES(‘SHA1′,’raresql’);
— Select Hasbytes value
Select @hashbytes as Hasbytes
–Convert varbinary value to varchar value
Set @varchar= CONVERT(VARCHAR(42), @hashbytes, 1) — Convert(datatype, value, style); style 1 has 0x, 2 doesnt
–Select varchar value
Select @varchar as [Varchar], LEN(@varchar)