Unusual scenario in datastage

soumya5891 · Post by **soumya5891** » Fri Dec 30, 2011 1:46 pm

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?

ray.wurlod · Post by **ray.wurlod** » Fri Dec 30, 2011 4:29 pm

Trim(many spaces) always returns one space, not none. This is documented behaviour. There are other functions for removing spaces.

soumya5891 · Post by **soumya5891** » Fri Dec 30, 2011 11:10 pm

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.

zulfi123786 · Post by **zulfi123786** » Mon Jan 02, 2012 12:09 am

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

soumya5891 · Post by **soumya5891** » Mon Jan 02, 2012 12:19 am

My target datatype is VARCHAR(4).

priyadarshikunal · Post by **priyadarshikunal** » Mon Jan 09, 2012 9:32 am

try trim with option 'A' or convert ' ' to ''

PhilHibbs · Post by **PhilHibbs** » Tue Jan 10, 2012 11:10 am

ray.wurlod wrote:Trim(many spaces) always returns one space, not none. This is documented behaviour.

I think that that statement is incorrect.

Code: Select all

Len( Trim( '   ' ) )

returns 0 for me.

Phil.

pandeesh · Post by **pandeesh** » Tue Jan 10, 2012 11:55 am

Phil,
And also try

Code: Select all

len('         ')

Now we can understand the behaviour

PhilHibbs · Post by **PhilHibbs** » Wed Jan 11, 2012 4:45 am

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.

pandeesh · Post by **pandeesh** » Wed Jan 11, 2012 8:35 am

Thanks Phil!!
I believe

Code: Select all

trim(many spaces)

returns no spaces.( i feel this is the usual behavior of trim).

chandra.shekhar@tcs.com · Thu Jan 12, 2012 1:11 am

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.

pandeesh · Post by **pandeesh** » Thu Jan 12, 2012 1:37 am

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)

chandra.shekhar@tcs.com · Thu Jan 12, 2012 4:04 am

@Pandeesh
The following example will make you understand

Code: Select all

trim('  IBM   DataStage  ') = 'IBM DataStage'

pandeesh · Post by **pandeesh** » Thu Jan 12, 2012 5:24 am

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!

chandra.shekhar@tcs.com · Thu Jan 12, 2012 7:54 am

That is also true.