'Sql cast to float without scientific notation

How can I cast a decimal value to float without getting the result in scientific notation?

For example, if my value is 0.000050 as a decimal, when I cast it to float I get 5E-05

I would like to see 0.00005

sqlsql-server


Solution 1:[1]

ALTER FUNCTION [dbo].[fnExpStringToDecimal]
(
     @Number AS varchar(50)
) Returns Decimal(18,7)
BEGIN 
RETURN (SELECT IIF(CHARINDEX ('E', @Number)> 0,CAST(SUBSTRING(@Number, 1, CHARINDEX ('E', @Number)-1)AS DECIMAL(18,7)) * POWER( 10.0000000,CAST(SUBSTRING(@Number, CHARINDEX ('E', @Number)+1, LEN(@Number)) AS DECIMAL(18,7))), @Number))
END

Solution 2:[2]

I know that's is not the correct answer, but if you are searching for a conversion from VARCHAR scientific notation to DECIMAL and landed here like me, on SQL SERVER you can do:

SELECT CAST('4.51432543543E-4' AS FLOAT)

(tested on 2012+)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Musa
Solution 2 Bruno Leitão

Related Questions