Calculate MD5 in Teradata

access_time 4 months ago visibility166 comment 0

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:


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:

The hashed value matches exactly with the Teradata one. 

copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn or Twitter.

Want to publish your article on Kontext?

Learn more

More from Kontext

visibility 104
thumb_up 0
access_time 6 months ago

The following code snippets show how to use round numbers to hundreds or thousands in Teradata.  warning Alert - The following code snippets are used to round to the smallest hundreds or thousands that is equal or greater than the input number.  FLOOR and CEILING fun ctions In ...

visibility 9
thumb_up 0
access_time 22 days ago

Teradata BIGINT data type represents a signed, binary integer value from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. To convert BIGINT to VARCHAR data type, CAST function can be used directly.  SEL CAST(-9223372036854775808 AS VARCHAR(100)) Result:  ...

visibility 227
thumb_up 0
access_time 5 months ago

XML data type is commonly supported in Teradata. Together with native XML type, a number of XML functions are added to support extracting values from XML, shredding and publishing JSON, etc.  info The following code snippets use string literal to demonstrate the usage of these functions; you ...