Calculate MD5 in Teradata

access_time 2 months ago visibility8 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

Want to publish your article on Kontext?

Learn more

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer teradata local_offer spark local_offer pyspark local_offer spark-database-connect

visibility 5202
thumb_up 0
access_time 2 years ago

In my article Connect to Teradata database through Python , I demonstrated about how to use Teradata python package or Teradata ODBC driver to connect to Teradata. In this article, I’m going to show you how to connect to Teradata through JDBC drivers so that you can load data directly into PySpark ...

local_offer teradata local_offer SQL local_offer teradata-sql-query

visibility 511
thumb_up 0
access_time 8 months ago

In Teradata, Common Table Expression (CTE) is supported as other databases. You can create recursive CTE or use a reference of a CTE to another CTE. However there is a slight differences compared with other databases - The referenced CTE must be present after the referencing CTE. For example, CTE ...

local_offer teradata local_offer SQL

visibility 843
thumb_up 0
access_time 12 months ago

This code snippet shows how to calculate time differences.

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS