Page 1 of 2
How to remove unwanted space at one column
Posted: Fri May 16, 2008 8:30 am
by dodda
Hi all,
Iam having a source as txt file with some columns. And Target is also txt file with transformer in between
In that, iam having one column called price with data 2495.07000000 as varchar(15).
I have convert it into decimal 19 with scale 8.
I used stringtodecimal function .When i ran the job ,everything is fine.
But a single unwanted space is appended to that column .
How can remove that space .
Plz help me out.
Re: How to remove unwanted space at the one column
Posted: Fri May 16, 2008 9:31 am
by ureddy
Use either Trim(columnname) or TrimB(columnname)
Posted: Fri May 16, 2008 10:34 am
by dodda
It's again showing the same space .
Can anyone give some suggestions to this one.?
Posted: Fri May 16, 2008 11:31 am
by ArndW
A decimal column doesn't have any spaces; just when displayed.
Posted: Fri May 16, 2008 11:36 am
by chulett
Isn't that just the placeholder for the sign the viewer adds?
Posted: Fri May 16, 2008 11:40 am
by ArndW
Yes, that is what the space in the display of view data or a peek stage is.
Posted: Fri May 16, 2008 12:31 pm
by swapnilverma
ArndW wrote:A decimal column doesn't have any spaces; just when displayed. ...
trim function should be working fine...
try this trim(stringtodecimal(column -name ) )
its should work
Posted: Sat May 17, 2008 12:35 am
by dodda
I used the trim function , But the space was not removed
Mysource column data is:::3.78875632
It is showing as " 00000000003.78875632"
Can anybody solve this one.
Your help is greatly apperciated
Posted: Sat May 17, 2008 2:09 am
by ray.wurlod
Read what Arnd already told you. If the data type is decimal there IS NO SPACE in the data - only when it is displayed is there a space for the (positive) sign character. If the data type is string then you can do with it whatever you wish.
Posted: Sat May 17, 2008 6:05 am
by dodda
Thanks Ray and Arnd.
Now i understand the solution.
But my testing team considered that space as a bug. They want to remove that space.
What should i do now?
Posted: Sat May 17, 2008 6:14 am
by chulett
Educate them. There is no space.
Posted: Sat May 17, 2008 4:10 pm
by ray.wurlod
It is an artifact of the decimal number being displayed. Would they object if, instead of the space, there was a "+" sign? The only way to eliminate the displayed decimal sign space is to change the data type to string (VarChar or Char) and then to use Trim() or Convert() to get rid of it.
When you are educating them, ask which part of "no space" they don't understand.
Posted: Tue May 20, 2008 8:36 am
by dodda
Hi all,
My target column datatype is changed to varchar 19.
Mysource column data is::: 3.78875632
My output should be ::: 00000000003.78875632
How Can i achieve the solution fo the above one?
Posted: Tue May 20, 2008 4:22 pm
by ray.wurlod
Code: Select all
Right(Str("0",19) : InLink.TheString, 19)
Posted: Tue May 27, 2008 12:40 am
by fc_user
I am facing a similar kind of issue. I have to create a fixed width file, so in the sequential file I don't want any spaces before the decimal fields. i.e. if my source is 10.00, I want the output as "10.00" and not " 10.00".
I tried converting the decimal to the string and trim the space, but the moment I convert it to varchar, The result is displayed as 000.00
I also tried using this function
IF ISNULL(Read_RadiantBODDetail.Payamount1) THEN '000.00' ELSE Right(Str("0",7) : Read_RadiantBODDetail.Payamount1, 7) but the output is 000.00 only.
Can anyone suggest a possible solution to this problem?