comparison of 2 columns of different datatypes giving undesired results

Database
Enthusiast

comparison of 2 columns of different datatypes giving undesired results

Greetings All,

When comparing 2 different char/varchar datatypes in TD , the comparison is returning undesired results as the hexadecimal representation of the data in columns is different eventhough the data is visibly same.  Not sure what the "00" in the 

hexadecimal notation represents. 

select col1, col2, char2hexint(col1), char2hexint(col2),
(case when nvl(TRIM(col1),'0')=nvl(TRIM(col2),'0') then 1 else 0 end) col3
from table1 inner join table2 on (colpi11=colpi22);

sample output:
col1 col2 hex(col1) hex(col2) col3
CO02558438 CO02558438 434F303235353834333800000000000000000000 434F3032353538343338 0
NULL 0000000000000000000000000000000000000000 NULL 0
Datatype of col1: char(20) character set latin not casespecific not null

datatype of col2: varchar(10) character set latin not casespecific

After googling could see that "00" in hexadecimal represents NULL;

But when I do

select col1 from table1 where col1 is NULL;  -- returns 0 rows
when I use the IS NOT NULL clause, could see the <blank> value with hexadecimal equivalent value as 40 zeroes.

I am stumped on this, can someone suggest on this behavior which is strange to me.  FYI, data is loaded to Teradata through Datastage, to which we don't have access.

Thanks,

Cheeli

1 REPLY
Senior Apprentice

Re: comparison of 2 columns of different datatypes giving undesired results

Hi Cheeli,

why do you post the same question using a new account?