How to remove unwanted space at one column

dodda · Post by **dodda** » Fri May 16, 2008 8:30 am

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.

ureddy · Post by **ureddy** » Fri May 16, 2008 9:31 am

Use either Trim(columnname) or TrimB(columnname)

dodda · Post by **dodda** » Fri May 16, 2008 10:34 am

It's again showing the same space .

Can anyone give some suggestions to this one.?

ArndW · Post by **ArndW** » Fri May 16, 2008 11:31 am

A decimal column doesn't have any spaces; just when displayed.

chulett · Post by **chulett** » Fri May 16, 2008 11:36 am

Isn't that just the placeholder for the sign the viewer adds?

ArndW · Post by **ArndW** » Fri May 16, 2008 11:40 am

Yes, that is what the space in the display of view data or a peek stage is.

swapnilverma · Post by **swapnilverma** » Fri May 16, 2008 12:31 pm

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

dodda · Post by **dodda** » Sat May 17, 2008 12:35 am

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

ray.wurlod · Post by **ray.wurlod** » Sat May 17, 2008 2:09 am

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.

dodda · Post by **dodda** » Sat May 17, 2008 6:05 am

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?

chulett · Post by **chulett** » Sat May 17, 2008 6:14 am

Educate them. There is no space.

ray.wurlod · Post by **ray.wurlod** » Sat May 17, 2008 4:10 pm

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.

dodda · Post by **dodda** » Tue May 20, 2008 8:36 am

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?

ray.wurlod · Post by **ray.wurlod** » Tue May 20, 2008 4:22 pm

Code: Select all

Right(Str("0",19) : InLink.TheString, 19)

fc_user · Post by **fc_user** » Tue May 27, 2008 12:40 am

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?

DSXchange

How to remove unwanted space at one column

How to remove unwanted space at one column

Re: How to remove unwanted space at the one column