All Forums Database
pankajcse1 2 posts Joined 07/15
28 Jul 2015
Greenpum to Teradata Migration ( UDF hash_MD5() issue )

We are having issue with implementing Change Data Capture through a UDF called hash_md5() which we created in our DB environment from the below link:- md5-message-digest-udf
Actually in teradata we found that there is no exact fucntion like MD5() like other database greenplum which can generate hash value(hexadecimal value) by concatenating the columns.So we tried to use the above UDF , but this function not accepting the some of the characters , which still acceptable in Teradata.We get error like (string contain Untranslatble chracters).
We use the function like select dba_work.hash_md5(col1||col2||col3||.......||col32)
It seems the above function has written long back and has limitation, since its no accepting the charcters which are still acceptable in DB.(our source and target DB all are in UTF8)
Could you please suggests is there any latest function for MD5() in Teradata or some other function available which can be use in this regard for CDC in stored procedure.

dnoeth 4628 posts Joined 11/04
28 Jul 2015

This UDF is defined for the LATIN charset and there's no Unicode version.
For a similar SHA-UDF i'm using a simple solution, the udf_utf16to8 UDF from the Unicode Tool Kit:


pankajcse1 2 posts Joined 07/15
29 Jul 2015

Hi Dieter,
Thanks for your suggestion, so just wanted to check that since our Teradata DB is in UTF8 format and also source DB in UTF8 format, so while using the the above UDF udf_utf16to8 with hash_MD5 can be able to handle the below characters :-
Sample Column value in table which causing error while using the UDF hash_MD5  :-

™ ‚— – ‹ ˆ » ƒ ›†ž‡
1 /zh_tw/飯店/台灣/台中/.h331267/
2 /ja_jp/ホテル/日本/京都--.i5508611/
3 /zh_tw/飯店/日本/京都/.h367771/
4 /zh_tw/飯店/印尼/é›…åŠ é”.hd29307/
5 /zh_tw/飯店/台灣/台中/.h1257211/
6 /ja_jp/ホテル/スペイン/バルセロナ--サンツ駅.hd5477031.p4/
7 /zh_tw/飯店/台灣/高雄/.h606931/
8 /zh_hk/酒店/泰國/曼谷/a.h194543/
9 /zh_tw/飯店/台灣/墾丁/.h343217/
Please advise on the above scenario.

dnoeth 4628 posts Joined 11/04
29 Jul 2015

Yes, hash_MD5(udf_utf16to8(col)) works with Unicode and returns the same result as a Unicode-MD5 (at least it's like that for SHA-256).  


You must sign in to leave a comment.