Format 195,231 (char) to 195231 (Numeric)
Moderators: chulett, rschirm, roy
Format 195,231 (char) to 195231 (Numeric)
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
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Code: Select all
Oconv(inlink.TheNumber, "MCN")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
Thanks in advance,
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
would like to know your advise..we could have given you a little more targetted advise.
Thanks in advance,
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
but it is not allowing me to insert the data in target table decimal column.
Any help is appreciated!
Thanks
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'))
Any help is appreciated!
Thanks
-
- Participant
- Posts: 232
- Joined: Sat May 07, 2005 2:49 pm
- Location: USA
Hi Titto,
Use the below transformation and see whether it works for you
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")
Thanks,
Naveen
Naveen
Still think math would do the trick...
Give that a shot.
Code: Select all
If Len(Trim((MYLINK.COST)) = 0 Then 0 Else MYLINK.COST * 1
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 232
- Joined: Sat May 07, 2005 2:49 pm
- Location: USA