Page 1 of 2
Unusual scenario in datastage
Posted: Fri Dec 30, 2011 1:46 pm
by soumya5891
I am facing one peculiar problem.In my source I have fixed length file.In a transformer I have taking out the various fields with proper length.
Now let say I have taking out one column and it is space.Now if I apply trim and write only that field in a dataset then one space is there instead of empty.But if we write any other column with that then it is working properly,it's coming as empty.
Can you please help me on this?
Posted: Fri Dec 30, 2011 4:29 pm
by ray.wurlod
Trim(many spaces) always returns one space, not none. This is documented behaviour. There are other functions for removing spaces.
Posted: Fri Dec 30, 2011 11:10 pm
by soumya5891
Hi Ray,
If I write only that field in dataset after trim then one space comes.But if I write any other field along with that field after trim then it comes as empty.I am getting confused in this scenario.
Posted: Mon Jan 02, 2012 12:09 am
by zulfi123786
What is the data type of the columns you are talking about ?. Trim - if the whole data is string of only spaces it returns empty, else it strips off the leading and trailing spaces and reduces multiple spaces embedded withing the charactes to single occurence as told by Ray
Posted: Mon Jan 02, 2012 12:19 am
by soumya5891
My target datatype is VARCHAR(4).
Posted: Mon Jan 09, 2012 9:32 am
by priyadarshikunal
try trim with option 'A' or convert ' ' to ''
Posted: Tue Jan 10, 2012 11:10 am
by PhilHibbs
ray.wurlod wrote:Trim(many spaces) always returns one space, not none. This is documented behaviour.
I think that that statement is incorrect.
returns 0 for me.
Phil.
Posted: Tue Jan 10, 2012 11:55 am
by pandeesh
Phil,
And also try
Now we can understand the behaviour
Posted: Wed Jan 11, 2012 4:45 am
by PhilHibbs
That returns 9, there are 9 spaces, but if you trim() it there will be 0 spaces. I don't understand what this demonstrates, other than that trim(many spaces) returns an empty string, not a single space, contrary to what Ray stated.
Posted: Wed Jan 11, 2012 8:35 am
by pandeesh
Thanks Phil!!
I believe
returns no spaces.( i feel this is the usual behavior of trim).
Posted: Thu Jan 12, 2012 1:11 am
by chandra.shekhar@tcs.com
As Zulfi has mentioned, trim functions removes the leading and trailing spaces and reduces multiple spaces embedded withing the characters to single occurrence. There should not be any confusions now.
Posted: Thu Jan 12, 2012 1:37 am
by pandeesh
chandra.shekhar@tcs.com wrote:multiple spaces embedded withing the characters to single occurrence.
Can you elaborate this part?
What i feel is, at the result of trim() the number of spaces will be zero(not even single)
Posted: Thu Jan 12, 2012 4:04 am
by chandra.shekhar@tcs.com
@Pandeesh
The following example will make you understand
Code: Select all
trim(' IBM DataStage ') = 'IBM DataStage'
Posted: Thu Jan 12, 2012 5:24 am
by pandeesh
yes Chandra!!
Its clearly derived that
Code: Select all
trim(many spaces)-->No spaces(leading and trialing ,not a single occurrence even).
This is what initially phil claimed!
Posted: Thu Jan 12, 2012 7:54 am
by chandra.shekhar@tcs.com
That is also true.
