Numbers reflect not in proper format in SQL

Question

Hello MS Community,

I have another issue. After running the following syntax the result in the column is not reflecting in a proper decimal number. Is there a workaround in SQL?

Example:
0. 000197

1. 4E-05
2. 000178
3. 000776
4. 007687
5. 004375
6. 004472
7. 000725
8. 6E-05
   3E-06
9. 006045
10. 9E-05
11. 00019
    4E-06

SELECT c.*, ROUND((("2020"-"2019")/"2019"),5) AS GrowthRate_pct, c."2020"/a.Land_Area_Km_sq "Density",
Land_Area_Km_sq, WorldLandMass_pct, SubRegName, SDGRegName, GeoRegName
INTO Table2
FROM CountryPopYoY c
INNER JOIN LandArea a
ON c.Country = a.Country
INNER JOIN LocationMapping m
ON c.Country = m.Location
ORDER BY Land_Area_km_sq DESC;
SELECT * FROM TABLE2

60021-table2.txt

Answer 1

Hi @Zarena Akbarshah ,

Welcome to Microsoft Q&A!

Please have a try with below:

CONVERT(numeric(8,8), WorldLandMass_pct) WorldLandMass_pct  

OR

CONVERT(numeric(8,8), CAST(WorldLandMass_pct AS FLOAT)) WorldLandMass_pct  

If above is not working, we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data and the expected output.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thanks, Melissa, you are a saviour! Both are working as per your suggestion. :)

SELECT c.*, ROUND((("2020"-"2019")/"2019"),5) AS GrowthRate_pct, c."2020"/a.Land_Area_Km_sq "Density",
Land_Area_Km_sq, CONVERT (NUMERIC (8,8),WorldLandMass_pct) WorldLandMass_pct, SubRegName, SDGRegName, GeoRegName
FROM CountryPopYoY c
INNER JOIN LandArea a
ON c.Country = a.Country
INNER JOIN LocationMapping m
ON c.Country = m.Location
ORDER BY Land_Area_km_sq DESC;

SELECT c.*, ROUND((("2020"-"2019")/"2019"),5) AS GrowthRate_pct, c."2020"/a.Land_Area_Km_sq "Density",
Land_Area_Km_sq, CONVERT(numeric(8,8), CAST(WorldLandMass_pct AS FLOAT)) WorldLandMass_pct, SubRegName, SDGRegName, GeoRegName
FROM CountryPopYoY c
INNER JOIN LandArea a
ON c.Country = a.Country
INNER JOIN LocationMapping m
ON c.Country = m.Location
ORDER BY Land_Area_km_sq DESC;