Convert scientific notation to decimal

Database

Convert scientific notation to decimal

I currently have a char field that contains scientific notation values. I would like to convert these to decimal.

Thanks in advance for your help!!

Example

Current format                           Needed Format

-1.9178464696202265E-2 -->     -0.01917846469620226500

4 REPLIES
Junior Contributor

Re: Convert scientific notation to decimal

CAST(col AS NUMBER) should work

Enthusiast

Re: Convert scientific notation to decimal

Cast?

sel Cast('-1.9178464696202265E-2' as Decimal(20,20));

-0.01917846469620226500

Rglass

Fan

Re: Convert scientific notation to decimal

I have a similar problem a VARCHAR type field whose value is 1122553344527489, on getting exported via BTEQ to an CSV file has value 1122553344527480 and gets displayed as 1.12255E+15. I am attaching a sample code which is used, Please help

.SET RECORDMODE OFF;
.SET FORMAT OFF ;
.SET TITLEDASHES OFF;
.SET SEPARATOR ',';


.export report file= I:\testresul.csv;
.set width 65531;

 

SELECT CAST(A.Roll_No AS NUMBER) AS Roll No,
A.Rank,
CAST(A.Adm_No AS NUMBER) AS Admission No FROM Sch_database as A

 

.EXPORT RESET
.logoff

 

The Roll No and Admission No columns are the VARCHAR fields both are around 16-20 digits but on exporting via BTEQ to CSV we are getting exponential values.

Junior Contributor

Re: Convert scientific notation to decimal

I don't know what causes this (works for me as expected), but why are you casting a Varchar to a numeric datatype when you want to export CSV?

 

Btw, this doesn't export CSV, it's fixed length, better use the CSV function or switch to TPT.