All Forums Database
chill3che 99 posts Joined 10/12
14 May 2015
Comparing 2 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.

 

Also, explicitly casting the col1 to equivalent length of the data in col2 is matching the result.

 

sel case when cast(col1 as varchar(10))=col2 then 1 else 0 end col3 from .....  -- giving result as expected

 

sel case when cast(col1 as varchar(25))=cast(col2 as varchar(25)) then 1 else 0 end col3 from ... -- still giving undesired result;  the hexadecimal format has still zeroes in the col1;

 

 

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

Thanks, Cheeli
dnoeth 4628 posts Joined 11/04
14 May 2015

Hi Cheeli,
a NULL in SQL is not a binary zero.
What you see seems to be a common problem when data is loaded from DB2, it's the so-called "low value". DB2 tends to add those instead of a proper blank '20'xc.
You need to TRIM(TRAILING '00'xc FROM col). But sometimes those low values seem to be within a string, too. So you better use oTRANSLATE(col, '00'xc, '') instead.
Best case would be cleaning the existing data and modifying the load process.
 
 
 
 

Dieter

You must sign in to leave a comment.