Convert chr(0) to chr(32) (or how to trim them)

whenry6000 · Post by **whenry6000** » Fri Apr 03, 2009 1:33 pm

All,
I am having issues with data that contains trailing chr(0) characters. I have tried TrimB, with $APT_ORACLE_PRESERVE_BLANKS set to both True and False and nothing seems to work. When I try to specify the character to use a TRIM with one of the options, I cannot seem to figure out a way to specify chr(0) as in Trim(string,chr(0),"T"). I also tried to convert the chr(0) to chr(32) as then a TrimB should work, but also can't figure out a way to do that....I can't get Datastage to recognize chr(0) as a string.

Can someone tell me the syntax for trimming something of this nature?

BugFree · Post by **BugFree** » Mon Apr 06, 2009 2:48 am

hi,

Which is the soucre system for these data?

Char(0) is nothing but the null character. So the TrimB function alone will not work as it is the function to remove the space character.
How did you tried to convert char(0) to char(32) ? The reason i am asking this question is, you said the data contains the trailing char(0) and if you are applying convert function to entire data will not serve the purpose.

Can you post the function that you have tried?

whenry6000 · Post by **whenry6000** » Mon Apr 06, 2009 6:27 am

BugFree wrote:hi,

Which is the soucre system for these data?

Char(0) is nothing but the null character. So the TrimB function alone will not work as it is the function to remove the space character.
How did you tried to convert char(0) to char(32) ? The reason i am asking this question is, you said the data contains the trailing char(0) and if you are applying convert function to entire data will not serve the purpose.

Can you post the function that you have tried?

The source system is Oracle. What seems to have happened is that the source is all declared as CHAR. This automatically pads it with chr(0). Then we end up having to apply a bunch of trims in the Oracle select during our transformations.

I tried convert(chr(0),chr(32),input_string) in the transformer, but it doesn't seem to recognize the chr(0) and chr(32) designations. I also tried one of the trim functions that accepts a string value as the character to trim, but again, it seems to be looking for a string.

I am not sure how to make Datastage recognize chr(0) or chr(32) as string or character values.

BugFree · Post by **BugFree** » Mon Apr 06, 2009 6:42 am

whenry6000 wrote:
The source system is Oracle. What seems to have happened is that the source is all declared as CHAR. This automatically pads it with chr(0). Then we end up having to apply a bunch of trims in the Oracle select during our transformations.

I tried convert(chr(0),chr(32),input_string) in the transformer, but it doesn't seem to recognize the chr(0) and chr(32) designations. I also tried one of the trim functions that accepts a string value as the character to trim, but again, it seems to be looking for a string.

I am not sure how to make Datastage recognize chr(0) or chr(32) as string or character values.

Any trim function on char type will not give the correct result. Even if you are able to trim, ultimately DS will try to pad Char(000) to match the length.
So the best way i can think of make the data type as VarChar
1) i.e. try making the data type as VARCHAR in source oracle stage itself.

2) Or the other way is just try your convert function as to use char(000) instead of char(0) and make sure that the result datatype be varchar.

whenry6000 · Post by **whenry6000** » Mon Apr 06, 2009 7:15 am

BugFree wrote:
whenry6000 wrote:
The source system is Oracle. What seems to have happened is that the source is all declared as CHAR. This automatically pads it with chr(0). Then we end up having to apply a bunch of trims in the Oracle select during our transformations.

I tried convert(chr(0),chr(32),input_string) in the transformer, but it doesn't seem to recognize the chr(0) and chr(32) designations. I also tried one of the trim functions that accepts a string value as the character to trim, but again, it seems to be looking for a string.

I am not sure how to make Datastage recognize chr(0) or chr(32) as string or character values.
Any trim function on char type will not give the correct result. Even if you are able to trim, ultimately DS will try to pad Char(000) to match the length.
So the best way i can think of make the data type as VarChar
1) i.e. try making the data type as VARCHAR in source oracle stage itself.

2) Or the other way is just try your convert function as to use char(000) instead of char(0) and make sure that the result datatype be varchar.

I forgot to add that the source is char, but my target is varchar. So char(000) is the ascii representation for the NULL in Datastage, as opposed to chr(0) in the Oracle SQL??

ray.wurlod · Post by **ray.wurlod** » Mon Apr 06, 2009 7:29 am

That was an incorrect assertion. Char(0) in DataStage represents the ASCII NUL character, just like in Oracle. In C this character is used as a string terminator character.

The internal, or "out of band", representation of NULL (unknown value) in DataStage is Char(128) except on Windows platforms that use this codepoint to represent the Euro symbol.

ShaneMuir · Post by **ShaneMuir** » Tue Apr 07, 2009 6:30 am

You could always add $APT_STRING_PADCHAR to the job which is padding the string and set it to 0x20. Then your trim functions will work.