Comparing 2 different datatypes giving undesired results

Database
Enthusiast

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

1 REPLY
Senior Apprentice

Re: Comparing 2 different datatypes giving undesired results

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.