SQL Server Update inserts different hash values than a select does

v1uwarro 于 2023-03-17 发布在其他

关注(0)|答案(2)|浏览(139)

I am trying to update a new column in my SQL Server user table with the hash of another column.

when I test the conversion with a select statement it will return the correct MD5 hash that I get from online hash generators.

select CONVERT(VARCHAR(32), HashBytes('MD5', 'valuetohash'), 2)

when I use this same conversion in an update statement as shown below I get a different value, inserted then the select statement with the same value hashed.

UPDATE users SET [newcolumn1] = CONVERT(VARCHAR(32), HashBytes('MD5', column1), 2)

what am I doing wrong?

sql-server

来源：https://stackoverflow.com/questions/41053863/update-inserts-different-hash-values-than-a-select-does

2条答案

按热度按时间

oug3syen1#

The value you have in users.column1 does not exactly match the value you are manually passing through HashBytes as a test. To confirm that this works when the values are the same, try:

DECLARE @users TABLE (
    column1 VARCHAR(100),
    newcolumn1 VARCHAR(32)
)

INSERT INTO @users
SELECT 'some text', NULL

SELECT CONVERT(VARCHAR(32), HashBytes('MD5', 'some text'), 2)
UPDATE @users SET newcolumn1 = CONVERT(VARCHAR(32), HashBytes('MD5', column1), 2)
SELECT newcolumn1 FROM @users

You'll see that the results you get from each SELECT are the same, because the values of 'some text' and @users.column1 are identical.

Try comparing your values first:

SELECT CASE WHEN column1 = 'expectedValue' 
            THEN 'MATCH' 
            ELSE 'DIFFERENCE' 
       END AS MatchCheck
FROM users

SELECT column1
FROM users
WHERE column1 = 'expectedValue'

If you get results from the first query where MatchCheck = 'MATCH' or results from the second query at all, then you should also get results form your UPDATE which give the hash you expect, as the values are the same.

As mentioned by ughai in the comments, it's most likely you have some spaces or non-printable characters in the values in your database which you are not including when you dry-run the hashing, hence the different results.

赞(0）回复(0）举报 2023-03-17

yeotifhr2#

Check data type from source & target columns, hashkey generally has varbinary(8000) data types.

赞(0）回复(0）举报 2023-03-17