Page 1 of 1
Using Trim - Even then getting Blank Spaces
Posted: Wed Nov 30, 2005 5:13 am
by udankar
Hi,
While extracting data from a table - I used TRIM to avoid unwanted blank spaces after the column value.
But I get space even after using TRIM and it fails to match with the look up file which does not have any unwanted blankspace - Subsequently my job fails.
What is the way out to remove the Blank space .
Thanks and regards,
Udankar
Posted: Wed Nov 30, 2005 5:26 am
by ArndW
The TRIM function will remove spaces, but it might not remove characters that are not spaces but display as such. In your job add an extra test output column that displays the ASCII value of the last character in your test string - use
Code: Select all
SEQ(In.YourColumnName[LEN(In.YourColumnName),1])
and I will be willing to wager money that the value is
not 32 decimal.
Posted: Wed Nov 30, 2005 7:05 am
by jasper
I'm not sure if this applies to datastage also, but in most db's you have to watch for the difference between varchar and char.
the string 'aa' as varchar(10) is not equal to 'aa' as char(10).
I think datastage works to same way.
Posted: Wed Nov 30, 2005 7:20 am
by ArndW
Jasper,
no, DataStage doesn't. There is no such thing as CHAR in DataStage, if a job reads CHAR columns they are padded with blanks. I think that in this case the TRIM() function isn't doing what is expected because the padding is not the normal space (char(32)) but nulls (char(000)) and that is why I asked the poster to test what values are actually in the string.
When working with PX jobs your comment about the differences between VarChar() and Char() are applicable.