string to number

[I_Server_Whale]

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.

[chunsli]

You can write a ruouting, or do conversion in SQL.
By the way, what database do you use?

chunsli

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_Server_Whale]

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.

[changming]

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.

if A is a number in a string format, in your derivation, put A/1. I am sure that will solve your poblem.
forexsample, the data is 00050.
after 00050/1
it become 50.
try it.

[chulett]

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.

[I_Server_Whale]

Hi Craig,

It is not " in flight". I'm writing to a target flat file. That is why, I what to know whether a string in the format can be 00000050 can be converted to 50.00.

I thought this was not a big of a deal, but it is proving otherwise.

Any help is very much appreciated.

Thanks!

Naveen.

[chulett]

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:

Code: Select all

FMT(YourField,"R2")

Says to right justify and round to two decimal places. I really don't think you need to take this step however.

[cmmurari]

Hi,

You may typecasting function StringToDecimal
Returns the given string in decimal representation
string (string)
[rtype (string)]
result (decimal)

Cheers,
Murali

[chulett]

Hi Murali, welcome.

I think you'll find that StringToDecimal is a PX function and not one that can be used (or is even really needed) in a Server job.

[ray.wurlod]

Try

Code: Select all

Oconv(TheString, "MD2")

"MD" here means "masked decimal, and 2 is the number of implied decimal places in the input.