hex to char conversion

Database
Enthusiast

hex to char conversion

Hi,

Could anyone help me to convert HEX to CHAR for given column. I knew we can pass the hex value and convert to char, here I want to pass the column name (e.g: CHAR_To_HEX) and get char value.

--this works

SEL

CHAR2HEXINT('2650Wilson Ave.') AS CHAR_To_HEX

_UNICODE '003200360035003000570069006C0073006F006E0020004100760065002E'XCV AS HEX_TO_CHAR

;

--this error out

SEL

CHAR2HEXINT('2650Wilson Ave.') AS CHAR_To_HEX

_UNICODE (CHAR_To_HEX)'XCV AS HEX_TO_CHAR

;

Tags (1)
6 REPLIES
Enthusiast

Re: hex to char conversion

Hello ,

Just wondering, if you got a resolution as I am facing the same problem now.

Thanks

Abhishek

Enthusiast

Re: hex to char conversion

Still looking for a quick solution to this myself.... Anyone got an update for this?

Senior Apprentice

Re: hex to char conversion

Sorry, but I don't think there's an easy/quick solution for this using just SQL.

 

The '_UNICODE' part of the above code is not a function, hence it fails when passing a column name to it. I can't see anything in the 16.10 manuals either to suggest a new function has been added.

 

This can be done with a UDF:

- assume the input is the unicode string, the length being a multiple of 4.

- work through each group of 4 characters, convert to the equivalent unicode character, building the output string

- return the built string

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: hex to char conversion

The udf_16BEHex2Char function included in the "Unicode toolkit" available from http://downloads.teradata.com implements this conversion.

Enthusiast

Re: hex to char conversion

Thanks that did help.  I actully installed the SyntaxEditor Code Snippet

udf_LatinHex2Char(,,,) Function.  Worked great!

IN case someone needs a similar process to strip some characters and convert back... this is the full sql syntax I used.
SyntaxEditor Code Snippet
trim(yourdb.udf_LatinHex2Char((oreplace( OREPLACE(char2hexint(rp.ACCOUNTNUMBER) , 'AD',''), '2D','')),'',' '))

SyntaxEditor Code Snippet
This is removing HEX VALUE 'AD' , and '2D'   dashes and some other character
 
 
Junior Contributor

Re: hex to char conversion

Maybe silly questions, but why do you convert the string to hex and then back to Latin for removing those characters?

 

 

Translate( 'AD2D'xc, '')

returns the same result, of course it's a Unicode string. When the target column is Latin it's automatically translated back, otherwise you can do it manually, either by using all Latin parameters

 

OTranslate(ACCOUNTNUMBER, Translate( 'AD2D'xc USING unicode_to_latin), Translate ('' USING unicode_to_latin)

or translating the result:

Translate( OTranslate(ACCOUNTNUMBER, 'AD2D'xc, '')  USING unicode_to_latin)