Calculate MD5 in Teradata

Teradata has no built-in MD5 function thus custom function needs to be implemented for calculating MD5. This article shows you how to do that using the MD5 message digest UDF provided on Teradata Downloads.

Prerequisites

Permission CREATE FUNCTION is required for creating UDF in Teradata. 

Download the UDF scripts

Navigate to MD5 Message Digest UDF, download the zip package (md5_20080530.zip) locally. Please note, Teradata user account is required before you can download the file.

There four files in the md5/src folder:

Install MD5 UDF

Follow these steps to install this UDF:

1. Unpack the archive and go to src directory
2. Start bteq command window and login to Teradata:
   
3. Run this statement in BTEQ:

.run file = hash_md5.btq

The output looks like the following:

Test the function

In the above BTEQ session, run the following command:

SELECT hash_md5('abc');

The output looks like the following:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT hash_md5('abc');

SELECT hash_md5('abc');

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

hash_md5('abc')
--------------------------------
900150983CD24FB0D6963F7D28E17F72

To verify the value, we can run MD5 in SQL Server:

SELECT HASHBYTES('MD5','abc');

The output looks like the following screenshot:

The hashed value matches exactly with the Teradata one.