python equivalent T-SQL format (hashing)

Question

How to hash in python an equivalent of the following (changing the sql part isn't possible, only to mimic the behavior in python):

SELECT HASHBYTES('SHA2_256', FORMAT(1234, ''))

output: 0x4F37C061F1854F9682F543FECB5EE9D652C803235970202DE97C6E40C8361766

which is different than:

SELECT HASHBYTES('SHA2_256','1234')
SELECT HASHBYTES('SHA2_256',CAST(1234 as VARCHAR(100)))

output: 0x03AC674216F3E15C761EE1A5E255F067953623C8B388B4459E13F978D7C846F4

How to achieve in python the first output? Currently:

from hashlib import sha256

sha256('1234'.encode()).hexdigest().upper()

output: 03AC674216F3E15C761EE1A5E255F067953623C8B388B4459E13F978D7C846F4

HABO · Accepted Answer · 2020-05-07 15:35:16Z

2

The result of Format( 1234, '' ) is an NVarChar:

select sql_variant_property(FORMAT(1234, ''), 'basetype');

You can cast the value to get the desired result:

SELECT HASHBYTES('SHA2_256',CAST(FORMAT(1234, '') as VARCHAR(100)));

answered May 7, 2020 at 15:35

HABO

15.9k5 gold badges44 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

thanks, I know, but as I said: changing the sql part isn't possible

@NunoSilva That would mean changing the snake code to have wider characters, no?

thanks good point. ok, will look into that. from the top of you head you know how to do it?

@NunoSilva sha256('1234'.encode("utf-16")[2:]).hexdigest().upper() seems to take care of a two-byte prefix that encode is adding.

it solves it, same result as sql hash with format output. thanks @HABO,