scientific notation to Decimal

[Former Member]

Hi Experts,

I used to get Scientic format numbers in CSV file and I wanted to convert Scientific to Decimals 17,3.

Every time we used to get CSV file with scientific format numbers.

All of the numbers are in the column in scientific notation eg 2.8873322531580925E-2 .

Any idea how to solve this issue, please let me know.

Regards, Rag

[Former Member]

I have tried below statement and the derived correctly with rounding value but we need Decimals, please see the below example and request you to help me on this.

decode(Query."AMOUNT" like '%E+%',
(cast( substr(Query."AMOUNT" ,1, (index(Query."AMOUNT", 'E+',1 ) - 1)), 'decimal(10,3)') *
power( 10, cast( substr(Query."AMOUNT", (index(Query."AMOUNT", 'E+',1 ) + 2), 2), 'Integer') )),
cast(Query."AMOUNT", 'decimal(10,3)'))

Input Amount Data is --> 806893.689986282 (8.07E+05)

8.07E+05

Output Amount data is --->806900

We required Amount is - 806893.690

[Julian_Riegel · Accepted solution]

Hi there,

have a look at this post - it should help you solve this by building a custom function:

http://bodataservices.blogspot.com/2011/09/scientific-notation.html

if (index($VarcharNumber,'E',1) IS NULL)
begin
Return to_decimal_ext($VarcharNumber,'.',',',38,12);
end
else
begin
Return to_decimal_ext(word_ext($VarcharNumber,1,'E'),'.',',',38,24) * power(10, to_decimal_ext(word_ext($VarcharNumber,2,'E'),'.',',',38,12));
end

Regards,

Julian

[Former Member · Accepted solution]

Hi Julian,

Thanks for your response....

I have tried for Custom Function as you mentioned but the data is not deriving as expected.

Regards,

Rag

[Julian_Riegel · Accepted solution]

If your Input is 8.07E+05 the correct decimal conversion is 807000.000 - this is also what the above custom function generates.

I have quickly tested it with said input:

Also, if I put in your original Number 806893.689986282, the output is correct:

I'm not sure if I then understand your requirement correctly, but I would assume you have to adapt your source input as

8.07E+05 != 806893.689986282 - you would never get back the original value, rounding already seems to take place in your source.

Regards,

Julian

[Former Member · Accepted solution]

Hi Julian,

Thanks for your valuable inputs and I was working fine.

Appreciated your help on this

Regards,

Rag