CODEWITHSUNDEEP
sqlsql-server
Jill
Jill

Reputation: 533

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

Upvotes: 9

Views: 30278

Answers (3)

Bruno Leitão
Bruno Leitão

Reputation: 811

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+)

Upvotes: 0

Musa
Musa

Reputation: 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

Upvotes: 0

Nicholas Carey
Nicholas Carey

Reputation: 74197

This has nothing to do with converting to float. It has to do with converting to text. You need to look at the str() function:

str( float_expression , total-width , number-of-decimal-places )

where

  • float-expression means what you think it means,
  • total-width is the total field width desired, including sign, decimal place, etc.
  • number-of-decimal-places is the number of decimal places displayed (0-16). If more than 16 is specified, the formatted value is truncated (not rounded) at 16 decimal places.

In your case, something like:

declare @value float = 0.000050
select str(@value,12,6)

should do you.

Edited to note: the str() function will not display anything in scientific notation. If the problem is that you want to trim trailing zeroes from the decimal value, you can do two things:

  • Use the format() function (SQL Server 2012 only):

    declare @x decimal(18,6) = 123.010000
select @x                        as x1 ,
       format(@x,'#,##0.######') as x2 , -- all trailing zeroes trimmed
       format(@x,'#,##0.000###') as x3   -- min of 3, max of 6 decimal places shown

  • use replace() and trim(). Works for any version of SQL Server.

    declare @x decimal(18,6) = 123.010000
select @x as x1 ,
       replace( rtrim(replace(convert(varchar(32),@x),'0',' ')) , ' ' , '0' )

Upvotes: 17

Related Questions