Checksum Md5 Hex for Synapse Analytics

Question

I have a client who is sending a control file with a md5 hex for their data file. They want an ack file with our own computed md5. We're running serverless and synapse sql pools. The md5 we get is base64. Is there some function i can turn around and get the md5 hex so it matches their expectation?

Answer 1

I guess you can try uding the HASHBYTES function to compute MD5 hash values. but, the HASHBYTES function in Synapse SQL Pools returns values in binary format, not directly in hexadecimal. To match your client's expectation of MD5 in hexadecimal format, you can use a combination of CONVERT and SUBSTRING functions. i hope u get what I'm saying,

let me show you how to calculate an MD5 hash in hexadecimal format from a string

DECLARE @InputString NVARCHAR(MAX) = 'YourDataString';

-- Calculate MD5 hash in binary format
DECLARE @BinaryHash VARBINARY(16);
SET @BinaryHash = HASHBYTES('MD5', @InputString);

-- Convert binary hash to hexadecimal format
DECLARE @HexHash NVARCHAR(32);
SET @HexHash = CONVERT(NVARCHAR(32), @BinaryHash, 2);

-- Display the result
SELECT @HexHash AS MD5Hex;

But I don't recommend using MD5 is its weak for cryptographic purposes due to vulnerabilities. If security is a concern, consider using a more secure hash function, such as SHA-256 or SHA-3.