Professional Web Applications Themes

significant figures - Microsoft SQL / MS SQL Server

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

  1. #1

    Default Re: significant figures

    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_winterbottomhotmail.com> wrote in message
    news:bejo1a$620v8$1ID-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 ytical 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.
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >
    >

    Stefan Gustafsson Guest

  2. #2

    Default Re: significant figures

    "Stefan Gustafsson" <stefan_ghome.se> wrote in message
    news:O4mHfpuRDHA.1576TK2MSFTNGP12.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.



    John Winterbottom Guest

Similar Threads

  1. Cutting figures out of one photo and putting on other
    By Brian Chinnock in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 11
    Last Post: December 20th, 07:15 AM
  2. InDEsign Newbie - Consecutive numbering of figures in a doent. How?
    By Adam_Hodge@adobeforums.com in forum Adobe Indesign Windows
    Replies: 3
    Last Post: August 16th, 04:52 PM
  3. Figures deleted in Word during pdf creation
    By ericwinegar@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 0
    Last Post: May 18th, 10:35 PM
  4. Auto-numbering figures and tables
    By david_francois_huynh@adobeforums.com in forum Adobe Indesign Windows
    Replies: 7
    Last Post: April 6th, 11:48 AM
  5. Trace and Show only Edges of Figures...???
    By ZR in forum Adobe Photoshop Elements
    Replies: 0
    Last Post: August 14th, 02:47 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139