This question may answer on many threads but I am unable to find answer specific to my problem.
Q: I am getting data from API (in json format) where all columns are coming as string and inserting into a table which has all columns as string and serving as source table.
Now, I am trying to cast data from that source to destination and making all necessary casting to insert data into destination table. But decimal (16,8) casting failed.
I debug issue at my end and found that during the fetching data from API which is returning the data into json format converting values in some unusual format.
For e.g. 0.00007 converting into 7E-05 and this is happening for many other rows.
I know I can fix this problem at API implementation level. But I asked to solve this at SQL server end. So I need a solution which should convert 7E-05 into 0.00007.
Advertisement
Answer
This unusual format is a rather usual scientific notation Wikipedia, read section “E-notation”
You see the E and a number meaning exponent.
"1E2" = 1 * 10^2 = 100 "1E-2" = 1 * 10^(-2) = 0.01
Try this out:
DECLARE @tbl TABLE(Numberstring VARCHAR(100));
INSERT INTO @tbl VALUES('100'),('1E2'),('1E-2'),('7E-05');
SELECT Numberstring
,CAST(Numberstring AS FLOAT)
,CAST(CAST(Numberstring AS FLOAT) AS DECIMAL(20,10))
FROM @tbl;
The result
100 100 100.0000000000 1E2 100 100.0000000000 1E-2 0,01 0.0100000000 7E-05 7E-05 0.0000700000
You can see, that the FLOAT type itself will display the last one in the scientific notation, while the cast to DECIMAL will return the number you are expecting.
I’d be happy with an upvote, but you should accept Shawn’s answer as it was earlier than mine 😀