"Stefan Gustafsson" <stefan_ghome.se> wrote in message
news:O4mHfpuRDHA.1576TK2MSFTNGP12.phx.gbl...useful and appreciated :)> Hey, nice to know that some of the things I post here are actually useful
calling> You are right, the code does not work properly for exact powers of 10.
> The easiest solution is definitely to subtract a small value beforesigfigs+floor(-1e-9-log10(abs(value)))))> floor.
> Like this:
> -- The result will have trailing zeros - use str directly
> STR(value, 10, convert(int,That will work! The cases where we have a result which is an exact power of>
> It is much better to add -1e-9 to the log instead of the value, that way
> everything will still work as expected even for very small or very large
> Of course it is also possible to use a something like the following ugly
> CASE expression:
> WHEN floor(-log10(abs(value))) = -log10(abs(value))
> THEN -log10(abs(value))-1
> ELSE floor(-log10(abs(value)))
> But I would not bother
10 are not common, but they do exist.
Normally I would do this on the client, except that in this case we need to
store the formatted result in the database. One possibility is to do the
formatting in a COM component and then update the database - but I hate all
that round-tripping, and the SQL solution is so neat! Thanks again for you