Why does SQL Server HASHBYTES work differently from Oracle STANDARD

Any idea why the two hashing functions in SQL Server and Oracle yield different results when hashing the non-breaking space character?

Oracle select standard_hash('a ', 'MD5') from dual; results in 25EF28EB5A5BE667C6222418E9E27E8E and doesn't match SQL select HASHBYTES ('MD5','a '); results in CE8F03020C81133B3A082F8051EB9FF6. Note the space after the input 'a' is a non-breaking space character.

Is there any good source that can lay out the differences?

CodePudding user response：

This appears to be a character set or "collation" issue, where Oracle is in UTF-8 and SQL Server is in Latin 1252. My solution was to CONVERT the value to Windows Latin-1252 in Oracle before calculating the hash: select STANDARD_HASH(CONVERT('a ','WE8MSWIN1252'), 'MD5') from dual.

To find my collation/character set I did SELECT collation_name FROM sys.databases WHERE name = 'MY_DB_NAME'; in SQL Server and select * from nls_database_parameters where parameter='NLS_CHARACTERSET'; in Oracle.

CodePudding user response：

First of all you need to establish which one is actually yielding "different" results:

Using your favourite search engine to find online hash generators and testing results.

External Resources

https://passwordsgenerator.net/md5-hash-generator/ - 99020CB24BD13238D907C65CC2B57C03

https://www.md5hashgenerator.com/ - 99020cb24bd13238d907c65cc2b57c03

https://www.miraclesalad.com/webtools/md5.php - 99020cb24bd13238d907c65cc2b57c03

SQL Server

select HASHBYTES ('MD5', 'a ')

SQL Server 2014 SP3 (12.0.6024.0) - 0x99020CB24BD13238D907C65CC2B57C03

SQL Server 2019 (15.0.2080.9) - 0x99020CB24BD13238D907C65CC2B57C03

Oracle (using https://dbfiddle.uk/)

select standard_hash('a ', 'MD5') from dual;

Oracle 21c - 0x99020CB24BD13238D907C65CC2B57C03

Oracle 18c - 0x99020CB24BD13238D907C65CC2B57C03

Oracle 11gR2 - "ORA-00904: "STANDARD_HASH": invalid identifier"

Conclusion

You can see that your Oracle produced answer differs even from other Oracle answers. What version of Oracle are you running? What other options are set, such as collation etc.?