string to number
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
string to number
Hi All,
I need to convert a CHAR field to a decimal field in my routine.
For example,
If I have the CHAR data as '000000500' then i need to convert it to
'5.00'. Similarly if I have '000001467' then i need to convert it to '14.67'.
Is there any function which does this? Any help is tremendously appreciated.
Thanks much,
Naveen.
I need to convert a CHAR field to a decimal field in my routine.
For example,
If I have the CHAR data as '000000500' then i need to convert it to
'5.00'. Similarly if I have '000001467' then i need to convert it to '14.67'.
Is there any function which does this? Any help is tremendously appreciated.
Thanks much,
Naveen.
Re: string to number
You can write a ruouting, or do conversion in SQL.
By the way, what database do you use?
chunsli
By the way, what database do you use?
chunsli
naveendronavalli wrote:Hi All,
I need to convert a CHAR field to a decimal field in my routine.
For example,
If I have the CHAR data as '000000500' then i need to convert it to
'5.00'. Similarly if I have '000001467' then i need to convert it to '14.67'.
Is there any function which does this? Any help is tremendously appreciated.
Thanks much,
Naveen.
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
/1
if A is a number in a string format, in your derivation, put A/1. I am sure that will solve your poblem.naveendronavalli wrote:hi,
I use MS-SQL Server but I need to do the conversion in Datastage not in Database.
I tried using 'Fix' But it doesnt work
Thanks!
Naveen.
forexsample, the data is 00050.
after 00050/1
it become 50.
try it.
Math on a string forces a numeric conversion under the covers. You would need to do this if you were populating a field heading to a database table, for instance. I prefer to multiply by one, but that's just me.
However, if this was still "in flight" in your job moving between transformers, you should be able to simply move the data from a character field to a numeric field and the conversion will automatically happen in a Server job.
However, if this was still "in flight" in your job moving between transformers, you should be able to simply move the data from a character field to a numeric field and the conversion will automatically happen in a Server job.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Should still be automatic if you define your target field in the Sequential File stage as Decimal with a scale of 2, you shouldn't have to do anything magical to get what you want.
If you want to have specific control over the format of the data, define the target field as Character or Varchar and then use the FMT function to format the data. There are a number of format codes you could leverage - R to right justify, Z to suppress leading zeroes to name a couple. At its simplest:
Says to right justify and round to two decimal places. I really don't think you need to take this step however.
If you want to have specific control over the format of the data, define the target field as Character or Varchar and then use the FMT function to format the data. There are a number of format codes you could leverage - R to right justify, Z to suppress leading zeroes to name a couple. At its simplest:
Code: Select all
FMT(YourField,"R2")
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Try "MD" here means "masked decimal, and 2 is the number of implied decimal places in the input.
Code: Select all
Oconv(TheString, "MD2")
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.