hexadecimal values and char- to and fro

Database

hexadecimal values and char- to and fro

Greetings Experts,

Can convert char to hexadecimal using char2hexint function.  When the column used in unicode or latin it becomes as 

create volatile table xyz(col1 varchar(10) character set unicode, 
col2 varchar(10) character set latin) on commit preserve rows;
insert into xyz values (
'123
xyz',
'123
xyz'); -- inserted same value with newline char in 2 columns
select col1, char2hexint(col1), col2, char2hexint(col2) from xyz;

col1 Char2HexInt(col1) col2 Char2HexInt(col2)
---------- ---------------------------------------- ---------- --------------------
123 xyz 003100320033000A00780079007A 123 xyz 3132330A78797A

why is that for Unicode columns it is showing in 4byte format for each character where as in latin columns it shows in 2byte format.  (Is it due to that fact the Unicode takes double the space)

sel char2hexint(oreplace(col1,'000A'XC,''), char2hexint(oreplace(col1,'0A'XC,'') from xyz;

Outpuut:
003100320033000A00780079007A 00310032003300780079007A

Can you explain why does '000A'XC doesn't work here..

hexadecimal to char conversions:

How to convert hexadecimal to char as I am missing something here.

sel udf_16BEHex2Char('003100320033000A00780079007A','','') ;
*** Failure 3706 Syntax error: expected something between '(' and the string '003100320033000A00780079007A'.
Statement# 1, Info =53
*** Total elapsed time was 1 second.

sel udf_16BEHex2Char(char2hexint(col1),'','') from xyz;
*** Failure 3706 Syntax error: expected something between '(' and the 'char2hexint' keyword.
Statement# 1, Info =35
*** Total elapsed time was 1 second.

Thank you for your time..

1 REPLY
Teradata Employee

Re: hexadecimal values and char- to and fro

The repertoire for XC literals defaults to the session default character set. It appears your default is _Latin so '000A'XC is two characters.

You can explicitly specify _Unicode '000A'XC or you could use delimited Unicode literal form U&'\000A' UESCAPE '\'

Have you installed the UDFs from the Unicode Toolkit? The function udf_16BEHex2Char is not present by default.