Format 195,231 (char) to 195231 (Numeric)

[Titto]

Hi,

Can any one pls provide Format function for following example
I need to format 195,231 char to 195231 numeric.
Any help is appreciated!

Thanks

[Sainath.Srinivasan]

If the comma symbol is not in the data, then a direct move will suffice.

[Titto]

but in my input data Coma (,) is part of data. !

[chulett]

Actually doesn't matter - just move it.

[Titto]

Craig,

I tried moving 195,231 (char value) to Numberic value of DB2 table,it did not get moved as 195231, it is moved as 0.

Thanks

[ray.wurlod]

Code: Select all

Oconv(inlink.TheNumber, "MCN") 

removes all non-numeric characters. Beware that this will remove any sign from the number.

[chulett]

AKA the DIGITS transform.

Titto, it would have been good to know you meant a numeric field in a database table - we could have given you a little more targetted advise.

[Titto]

Hi Craig,

I should have mentioned Numeric fiele as target table. I did not get DIGIT transform as mentioned in your earlier reply, would you please explain it.
Here is what i am trying to do. I have Char field with value 195,231 and in some of the as 21- (negative value), what is the best way to move as 195231 numeric as you mentioned

we could have given you a little more targetted advise.

would like to know your advise..

Thanks in advance,

[Sainath.Srinivasan]

Maybe a simple Trim(YourNumber, ',', 'A')

[chulett]

If you open up the Manager and look at the Transforms branch, you should see a number of predefined transforms that (typically) are used to make calls to underlying functions "easier" to understand.

So, under the 'Built-In/String' sub-cat you'll see three - one of which is DIGITS and which does exactly what Ray posted. They are equivalent - they remove all non-numeric characters from a string so all you are left with is 'digits' - but which makes it more obvious what is going on?

If you are sure that the data in your character field will always be appropriate for a numeric database field and it's just the formatting characters you are worrying about, perhaps an old 'trick' will help. In the derivation of the database field, multiply the incoming character value by 1... the math forces an explicit conversion 'under the covers' to a numeric format and may solve your problem.

[Titto]

Hi ,
Craig, As I looked into the date in different file, Data is coming sometimes as spaces/blanks.
Can any one help - situation is as follows
I have Target DB column defined as DECIMAL(12,2) and source is Char filed with 123,345 or 2345- or blank - kind of data. I need to convert it to target db decimal value.
Eg:-
123,345 =======> 123345.00
2345- ========> 2345-
[blank/nulls] ========> 0.00

I am using

Code: Select all

IF Trim(MYLINK.COST,',','A') = ' ' THEN 0
             ELSE NullToZero(Trim(MYLINK.COST,',','A'))

but it is not allowing me to insert the data in target table decimal column.

Any help is appreciated!

Thanks

[pnchowdary]

Hi Titto,

Use the below transformation and see whether it works for you

Code: Select all

If IsNull(MYLINK.COST) Then "0.00" Else Trim(MYLINK.COST,",","A")

[Titto]

Chowdary!
nope it is not working.. still the same problem...
-902

[chulett]

Still think math would do the trick...

Code: Select all

If Len(Trim((MYLINK.COST)) = 0 Then 0 Else MYLINK.COST * 1

Give that a shot.

[pnchowdary]

Hi Craig,

That was a nice little trick . Can't we use the Real function in this scenario?