Raymond Raymond

Calculate MD5 in Teradata

event 2020-09-21 visibility 4,000 comment 0 insights toc
more_vert
insights Stats

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:

2020092153559-image.png

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:
    2020092154558-image.png
  3. Run this statement in BTEQ:
.run file = hash_md5.btq

The output looks like the following:

2020092154851-image.png

infoYou can edit md5.h to customize hash generation:
UDF_MD5_UPPERCASE: if set to 1 (default), generate MD5 result in upper caseUDF_MD5_COMPAT

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:

2020092155406-image.png

The hashed value matches exactly with the Teradata one. 

More from Kontext
comment Comments
No comments yet.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts