Sql cast to float without scientific notation

Question

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

Answer 1

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