Search code examples
sqlsql-serversignificant-digits

SQL - return all float values in field without writing in scientific notation

I have a column with an extremely large set of values (>21k), with an undetermined amount of decimal places, some sample data (FLOAT) that exists:

6.46
0.784
8.05
86.4
2.64
0
2.14
1E-06
14.81
3.45
0.87243
0
1.12279
0
21.41
0.6243

I would like to return all values as shown EXCEPT "1E-06" should be .000001; further, any scientific notation should be in a purely decimal format.

I have tried to write some sort of CASE statement where the "THEN" is followed by "CONVERT(varchar(100), CAST(@testFloat AS decimal(38,x)))" where x is the amount of sig figs, but I was unable to get the right condition for the WHEN statement, here was my best try:

CASE
    WHEN @testFloat = 0 THEN @testFloat
    WHEN FLOOR(@testFloat*10) != @testFloat*10 THEN CONVERT(varchar(100), CAST(@testFloat AS decimal(38,2)))
    WHEN FLOOR(@testFloat*100) != @testFloat*100 THEN CONVERT(varchar(100), CAST(@testFloat AS decimal(38,3)))
    *etc*
END

Pretty sure I wasn't even on the right trail....

Solution

  • Jeroen's comment was helpful, and gave me the expression to give me the desired results: 

    select FORMAT([sampleField], '0.' + REPLICATE('#', 308))

    Where I think I just have to bring down the '308' to a number that represents the highest level of precision seen in our processes. Thank you Jeroen for that solution!