如何在SQL Server中正确对长破折号（em dash）进行哈希？

The issue you're hitting boils down to character encoding missteps in your SQL Server code. Let's break this down and fix it step by step.

Why the Hash Doesn't Match #

In your T-SQL query, you’re converting the Unicode em dash (NCHAR(8212)) to VARCHAR(MAX) before passing it to HASHBYTES:

CONVERT(VARCHAR(128),HASHBYTES('SHA2_512', CONVERT(VARCHAR(MAX), NCHAR(8212))), 2)

VARCHAR relies on a single-byte code page (like the default SQL_Latin1_General_CP1_CI_AS), which doesn’t support the em dash character (U+2014). When you convert the Unicode NCHAR(8212) to VARCHAR, SQL Server replaces the em dash with a generic replacement character (usually a ?), which is why your hash result doesn’t align with Databricks or online tools.

Solution 1: Correct Unicode Hash (SQL Server Native) #

If you just need SQL Server to compute the accurate hash for the Unicode character (without matching UTF-8-based tools), remove the unnecessary CONVERT(VARCHAR(MAX)) call. Use the NVARCHAR value directly:

SELECT CONVERT(VARCHAR(128), HASHBYTES('SHA2_512', NCHAR(8212)), 2) AS CorrectUnicodeHash;

This computes the hash using SQL Server’s native UTF-16LE encoding for Unicode strings. Note this result will still differ from Databricks/online tools, since those use UTF-8 encoding by default.

Solution 2: Match UTF-8 Hash Results (Like Databricks/Online Tools) #

To get the exact same hash as Azure Databricks and online SHA-512 tools, you need to convert the Unicode em dash to UTF-8 encoded bytes before hashing in SQL Server. Here’s how to do it:

For SQL Server 2016+ (with built-in UTF-8 support) #

Use CONVERT with a UTF-8 collation to generate UTF-8 bytes:

SELECT CONVERT(VARCHAR(128), 
               HASHBYTES('SHA2_512', 
                         CONVERT(VARBINARY(MAX), NCHAR(8212) COLLATE Latin1_General_100_CI_AS_SC_UTF8)), 
               2) AS UTF8MatchingHash;

For Older SQL Server Versions (Pre-2016) #

If you’re on a version without native UTF-8 support, you can use the known UTF-8 byte sequence for the em dash (0xE2 0x80 0x94) directly:

SELECT CONVERT(VARCHAR(128), HASHBYTES('SHA2_512', 0xE28094), 2) AS UTF8MatchingHash;

Verification #

Running either of the Solution 2 queries will produce the same hash as Azure Databricks:
48bf9ce5ab3755bf779032043c2ff6b0b075ec3407b3e473872ea2b059165a86bd70e0854ff6e9d05f1677a77fefcce7c6e3d73d1f3d9dd6488a2fecf041bb89

Key Takeaway #

Always pay close attention to character encoding when comparing hashes across systems. SQL Server uses UTF-16 for Unicode strings, while most other tools (including Databricks) default to UTF-8. To align results, ensure you’re hashing the exact same byte sequence across all platforms.

内容的提问来源于stack exchange，提问作者Steven