All Forums Database
chill3chee 6 posts Joined 11/14
14 May 2015
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.





dnoeth 4628 posts Joined 11/04
14 May 2015

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


You must sign in to leave a comment.