Page 1 of 1
Regarding Character field
Posted: Thu Oct 09, 2008 11:22 am
by das_nirmalya
I am populating one CHAR(6) filed of a table from another tables Varchar2 filed.
But after poulating done when i am trying to fire a query in Oracle with
where cond <Column_name>='value' , though value exist in table , yet it showing no record. Reason is that there may be some space etc padded with the value.
I have tried with trim,StripWhiteSpace in DataStage, pls give solutions
Posted: Thu Oct 09, 2008 12:25 pm
by chulett
A CHAR field automatically pads with spaces to the full length, that's the nature of the beast. Either match the field size in your test, use a 'like' wildcard comparison or trim it
in the expression.
Posted: Mon Oct 13, 2008 12:11 pm
by bcarlson
Most databases will allow you to search without the trailing spaces, at least I know DB2 and Teradata do this:
Code: Select all
select count(*) from mytable where codefield = 'ABC'
is equivalent to
Code: Select all
select count(*) from mytable where codefield = 'ABC '
as long as the whitespace at the end is truly spaces. My guess is your problem is not trailing spaces but padding that is NOT spaces. We have had instances where if we did not explicitly specify the pad character as a space, it was padding with hex 00 (NULL). Check the value of APT_STRING_PADCHAR and make sure it is set to a space.
You can also test the hex values in the data in the database to see what is actually in the field. In Teradata the function is char2hexint(char-field), and on DB2 the function is hex(char-field). If the end of your char field is populated with 202020 (hex 20 is an ASCII space), then it is fine. If it is populated with 000000 (hex 00 is an ASCII null char) or some other value, then that is the issue.
Hope this helps...
brad.
Posted: Mon Oct 13, 2008 12:52 pm
by chulett
[sigh] True, true, true. Oracle as well. It was a long day and a late night, guess we need the equivalent of gmail's "Mail Goggles" here. Or just I do.
I would agree, the padding more than likely isn't spaces in this particular case.
Posted: Mon Oct 13, 2008 12:56 pm
by bcarlson
Try coffee. I've heard rumors that some find it very good and mentally stimulating. Just rumors, though
Brad.
Posted: Mon Oct 13, 2008 1:00 pm
by Mike
Brad,
Good tip about APT_STRING_PADCHAR.
Having a DB2 background when I first used Oracle many years ago, there are too many strange Oracle-isms to generalize. For instance, your example holds true if codefield is defined as CHAR. It does not hold true if codefield is defined as VARCHAR2. In DB2, your example works when codefield is CHAR or VARCHAR.
The most annoying Oracle-ism for me is the way that it interprets an empty string to mean NULL
.
Mike