Convert to Hex

General

Convert to Hex

Hi,

I am new to the forum and topic teradata generally esou trying to do a conversion of a varchar column to hex, but I'm not getting a satisfactory result. In Oracle to convert a hex to varchar (select to_char ('141865918893551308', 'fmXXXXXXXXXXXXXXXXX')) I get the result '01F8024D35626ECC', which is correct, but in teradata to perform this conversion (SELECT CHAR2HEXINT ('141865918893551308')) the result is another '003100340031003800360035003900310038003800390033003500350031003300300038' how can I get the result '01F8024D35626ECC' ?

Tags (1)
6 REPLIES
Senior Apprentice

Re: Convert to Hex

Your example will not work as-is in Oracle.

What is the datatype of your input, VarChar, VarByte or any INT?

And what do you want as output, VarChar or VarByte?

In TD14 you might utilize TO_BYTES/FROM_BYTES.

Re: Convert to Hex

Hello Dieter, thanks for the feedback,

My example would be a VARCHAR2 in Oracle (select to_char ('141865918893551308', 'fmXXXXXXXXXXXXXXXXXXX') from dual), this example works perfectly on Oracle. The return would be a varchar.

Would have an equivalent example in Teradata?

Senior Apprentice

Re: Convert to Hex

I didn't know you can apply TO_CHAR to a CHAR :)

Hopefully you're on TD14, TO_BYTES returns BYTE which can be used as input to FROM_BYTES to get a VARCHAR again:

SELECT FROM_BYTES(TO_BYTES ('141865918893551308', 'base10'), 'base16')

Re: Convert to Hex

My version is 13.10.05.04. Is there another way?

Tks,

Re: Convert to Hex

RELEASE    13.10.05.03

LANGUAGE SUPPORT MODE    Standard

VERSION    13.10.05.04

Senior Apprentice

Re: Convert to Hex

There's no built-in way to cast from/to bytes besides those new functions in TD14.

You need to write a C-UDF.