The following SQL
SELECT CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', 'test'), 2) as 'From String'
produces the following hash:
EE26B0DD4AF7E749AA1A8EE3C10AE9923F618980772E473F8819A5D4940E0DB27AC185F8A0E1D5F84F88BC887FD67B143732C304CC5FA9AD8E6F57F50028A8FF
If I then replace 'test'
with the column value from [Key] instead like this:
SELECT CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', [Key]), 2) as 'From Column' FROM MyTable
WHERE [Key] = 'test'
The following hash is produced:
9F7D8627E02F97CC5A52DCB2BA96038FE12F2A34B0FAC50E041359AE13D5EDE8A8A50562DA58BA7916DA378E7343EF91E85EFBD6A0A70AB237ADA4C2274DF13D
Right now we have a couple of rows in our database that I would like to hash, so I want to run the following code:
UPDATE MyTable SET [Key] = CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', [Key]), 2)
But the problem is that the hash produced is not correct, it's only correct if I replace [Key]
with the actual string value.
I found this question: Using HASHBYTES() yields different results for nvarchar and a variable and there they "solve it" by prefixing the string with N
. But I want to do it the other way around, I want the output to be
EE26B0DD4AF7E749AA1A8EE3C10AE9923F618980772E473F8819A5D4940E0DB27AC185F8A0E1D5F84F88BC887FD67B143732C304CC5FA9AD8E6F57F50028A8FF
.
How can I achieve that?
My table looks like this:
CREATE TABLE [dbo].[MyTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Key] [nvarchar](128) NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
SELECT CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', CAST([Key] as varchar(128))) ,2) as 'From Column' FROM MyTable WHERE [Key] = 'test'
Is that a good solution though?HASHBYTES
will have a different result for VARCHAR compare to NVARCHAR. Your table is NVARCHAR and you want the result of VARCHAR. Does not make sense.