significant figures

[Stefan Gustafsson]

Hey, nice to know that some of the things I post here are actually useful
:-)

You are right, the code does not work properly for exact powers of 10.

The easiest solution is definitely to subtract a small value before calling
floor.

Like this:

-- The result will have trailing zeros - use str directly
STR(@value, 10, convert(int, @sigfigs+floor(-1e-9-log10(abs(@value)))))

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
values

Of course it is also possible to use a something like the following ugly
CASE expression:

CASE
WHEN floor(-log10(abs(@value))) = -log10(abs(@value))
THEN -log10(abs(@value))-1
ELSE floor(-log10(abs(@value)))
END

But I would not bother

/SG

"John Winterbottom" <john_winterbottom@hotmail.com> wrote in message
news:bejo1a$620v8$1@ID-185006.news.dfncis.de...

> Some time ago I was give a very useful snippet of code by Stefan

Gustaffson

> to format a numeric value as a string with a given number of significant
> figures or digits. (This is a common requirement in analytical chemistry

and

> other fields).
>
> That code, turned in to a function, looks like this:
>
> CREATE FUNCTION fSigFigs
> (@value FLOAT,
> @sigfigs SMALLINT)
>
> /************************************************
> This function returns a string representation of
> a numeric value, formatted to a specific number
> of significant figures.
>
> Code courtesy of Stefan Gustaffson.
> **************************************************/
>
> RETURNS VARCHAR(25)
> AS
> BEGIN
> DECLARE @strValue VARCHAR(25)
> SET @strValue =
> CASE WHEN @sigfigs+floor(-log10(abs(@value))) <= 0 THEN
> -- The result will have no trailing zeros - round first and then use

str

> STR(round(@value, convert(int,
> @sigfigs+floor(-log10(abs(@value+1e-9))))), 10, 0)
> ELSE
> -- The result will have trailing zeros - use str directly
> STR(@value, 10, convert(int, @sigfigs+floor(-log10(abs(@value+1e-9)))))
> END
> RETURN @strValue
> END
>
>
> The problem is that it doesn't work for values which are exact powers of

10.

> E.g.
>
> SELECT dbo.fSigFigs(123.456, 3) = '123' -- correct
> SELECT dbo.fSigFigs(123.456, 1) = '100' -- correct
> SELECT dbo.fSigFigs(123.456, 5) = '123.46' -- correct
>
> but
>
> SELECT dbo.fSigFigs(100.000, 3) = '100.0' -- incorrect
>
> I know why it's incorrect - because we are using negative logs for our
> comparison,
>
> If we do
> select floor(-log10(abs(100))) we get 2,
>
> but if we do
> select floor(-log10(abs(100.1))) we get 3
>
> I haven't found a solution to this, (other than the horrible one of adding

a

> very tiny number like 10e-9 to the value parameter). If anyone knows an
> alternate method or can recommend a fix for this method I'd be very
> grateful.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

[John Winterbottom]

"Stefan Gustafsson" <stefan_g@home.se> wrote in message
news:O4mHfpuRDHA.1576@TK2MSFTNGP12.phx.gbl...

> Hey, nice to know that some of the things I post here are actually useful
> :-)

useful and appreciated :)

> You are right, the code does not work properly for exact powers of 10.
>
> The easiest solution is definitely to subtract a small value before

calling

> floor.
>
> Like this:
>
> -- The result will have trailing zeros - use str directly
> STR(@value, 10, convert(int,

@sigfigs+floor(-1e-9-log10(abs(@value)))))

>
> 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
> values
>
> Of course it is also possible to use a something like the following ugly
> CASE expression:
>
> CASE
> WHEN floor(-log10(abs(@value))) = -log10(abs(@value))
> THEN -log10(abs(@value))-1
> ELSE floor(-log10(abs(@value)))
> END
>
> But I would not bother
>

That will work! The cases where we have a result which is an exact power of
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
help.