Geek Answers Handbook

Sql rushes to swim without scientific notation

How to make a decimal value float without getting a result in scientific notation?

For example, if my value is equal 0.000050to a decimal, when I throw it in a float, I get5E-05

I would like to see 0.00005

+4
source share
2 answers

This has nothing to do with converting to float. This is due to conversion to text. You need to see str():

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

Where

  • float-expression means you think it means
  • total-width - the total width of the field, including the sign, decimal place, etc.
  • -- - (0-16). 16, ( ) 16 .

- :

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

.

: str() . , , :

  • format() ( SQL Server 2012):

    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

  • replace() trim(). SQL Server.

    declare @x decimal(18,6) = 123.010000
select @x as x1 ,
       replace( rtrim(replace(convert(varchar(32),@x),'0',' ')) , ' ' , '0' )
+8
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
0

More articles:


All Articles