When I actually get the HEXINT value for that record on that column, it was returning ‘00000000000000000000’. Now actually we were able to get the hex character loaded in that particular column and it is “x’00’”. This Hex null is somehow loaded into Teradata table through datastage jobs.
VND_AP_NUM CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
SEL vnd_ap_num FROM vendor.vnd
vnd_num = 1548 and vnd_ap_num is null;
sel chars(trim(vnd_ap_num)) from vendor.vnd where vnd_num = 1548;
SEL chars(trim(oreplace(vnd_ap_num,null,''))) FROM vendor.vnd
vnd_num = 1548;
Why would multiple null be accepted in a Not null column?
'00'xC is not a Teradata NULL. You can insert '00'xC in a NOT NULL CHAR column.
oReplace is returning white spaces for the '00'xC (it does nor replace them at all) and the trim removes them. That's why you see 0 as result.
BTW: I don't think you can use NULL as a parameter in oReplace with the results that you expect.