2

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....

Paniom
  • 25
  • 7
  • What is the data type of the column? – Gordon Linoff Feb 15 '19 at 19:13
  • The datatype of the column is Float, 'not null' more specifically. – Paniom Feb 15 '19 at 19:15
  • `FLOAT` has no real notion of "significant figures" in decimal (it's all rounded binary digits anyway), but to a first approximation, `select FORMAT(1e-6, '0.' + REPLICATE('#', 308))` might serve. This will avoid `E` notation completely, at the cost of producing some truly unwieldy notation for very big/small numbers. (The `308` can be reduced in practice if your numbers are never going to be that extreme, which possibly speeds up the formatting.) – Jeroen Mostert Feb 15 '19 at 19:34
  • @JeroenMostert I realized "sig figs" wasn't even really my issue as .000001 only has 1 sig fig anyway. I removed that part of my title. Secondly, this is a sample of a very large table that currently has >21k rows. I am hoping to write a query that would handle all values in that column as described above, simply meaning all values don't show scientific notation, no matter how small (or large) the value). – Paniom Feb 15 '19 at 19:41
  • Well, with 308 `#` signs, even the smallest (or largest) values won't use scientific notation, as that covers the entire range of `FLOAT`. (Thinking about it some more, though, that's just the extreme exponents -- you need more values for the significand at the extreme end. Make it 400 to be sure, why not...) I'm not aware of any shorter expression that will achieve the same effect. `FORMAT` uses the .NET format strings, documented [here](https://learn.microsoft.com/dotnet/standard/base-types/custom-numeric-format-strings), if you want to take a stab at it yourself. – Jeroen Mostert Feb 15 '19 at 19:46
  • @dnoeth: that's not actually necessary -- a single `0` or `#` will take care of the part before the decimal period, no matter how large. After the period it's a different game. – Jeroen Mostert Feb 15 '19 at 20:18
  • Ops, of course, you're correct. – dnoeth Feb 15 '19 at 20:19
  • @JeroenMostert - I think I see where I was confused and communication breaks down. Where you wrote `1e-6` in your code snippet, I would just instead put my desired field, correct? I will try your solution after the weekend. If you are confident of your solution I would write it as an answer, this is going to get flagged for 'conversation' soon anyways. – Paniom Feb 15 '19 at 21:26

1 Answers1

0

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!

Paniom
  • 25
  • 7