Professional Web Applications Themes

printf-like function in mysql - MySQL

Hi, I am trying to format my number, so that it shows 2 places after the decimal point, even when they are 00. I found the FORMAT() function, but it will add thousands-separators, and I do not want those. For numbers coming straight from the database, it works fine, for they are DECIMAL(6,2). The problem arises with fields that are calculated from these values. What I want would be 'printf ("%.2f", value)' in C. Any suggestions? Bart...

  1. #1

    Default printf-like function in mysql

    Hi,

    I am trying to format my number, so that it shows 2 places after the
    decimal point, even when they are 00. I found the FORMAT() function, but
    it will add thousands-separators, and I do not want those. For numbers
    coming straight from the database, it works fine, for they are
    DECIMAL(6,2). The problem arises with fields that are calculated from
    these values.

    What I want would be 'printf ("%.2f", value)' in C.

    Any suggestions?
    Bart
    Bart Friederichs Guest

  2. #2

    Default Re: printf-like function in mysql

    Bart Friederichs wrote:
    > Hi,
    >
    > I am trying to format my number, so that it shows 2 places after the
    > decimal point, even when they are 00. I found the FORMAT() function, but
    > it will add thousands-separators, and I do not want those. For numbers
    > coming straight from the database, it works fine, for they are
    > DECIMAL(6,2). The problem arises with fields that are calculated from
    > these values.
    >
    > What I want would be 'printf ("%.2f", value)' in C.
    >
    > Any suggestions?
    > Bart
    Something like this?

    select x from t1;
    +------+
    | x |
    +------+
    | 2 |
    | 3 |
    | 4 |
    +------+

    select x + 0.00 from t1;
    +----------+
    | x + 0.00 |
    +----------+
    | 2.00 |
    | 3.00 |
    | 4.00 |
    +----------+

    select x * 1234 + 0.00 from t1;
    +----------------+
    | x * 1234+ 0.00 |
    +----------------+
    | 2468.00 |
    | 3702.00 |
    | 4936.00 |
    +----------------+

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.blogspot.com/[/url]
    Giuseppe Maxia Guest

  3. #3

    Default Re: printf-like function in mysql

    Giuseppe Maxia wrote:
    > Bart Friederichs wrote:
    >> What I want would be 'printf ("%.2f", value)' in C.
    >>
    > Something like this?
    <snip solutions>

    I tried a little harder in finding a solution, and ROUND(value, 2) did
    the trick for me.

    Cheers,
    Bart
    Bart Friederichs Guest

  4. #4

    Default Re: printf-like function in mysql

    Bart Friederichs wrote:
    > Giuseppe Maxia wrote:
    >
    >>Bart Friederichs wrote:
    >>
    >>>What I want would be 'printf ("%.2f", value)' in C.
    >>>
    >>
    >>Something like this?
    >
    >
    > <snip solutions>
    >
    > I tried a little harder in finding a solution, and ROUND(value, 2) did
    > the trick for me.
    >
    > Cheers,
    > Bart
    Be aware that ROUND does not always return what you expect, depending
    on several factors.

    [url]http://dev.mysql.com/doc/refman/5.0/en/precision-math-rounding.html[/url]

    What I suggested is just a harmless trick that can't change the result.
    OTOH, ROUND could change the results, so be careful.

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.blogspot.com/[/url]
    Giuseppe Maxia Guest

  5. #5

    Default Re: printf-like function in mysql

    Giuseppe Maxia wrote:
    > Bart Friederichs wrote:
    >>
    >> I tried a little harder in finding a solution, and ROUND(value, 2) did
    >> the trick for me.
    > [url]http://dev.mysql.com/doc/refman/5.0/en/precision-math-rounding.html[/url]
    >
    > What I suggested is just a harmless trick that can't change the result.
    > OTOH, ROUND could change the results, so be careful.
    Thanks for the link. The ROUND() function is only used in a SELECT,
    after all calculations are done. The worst case in this application is a
    difference of 1 cent in money amounts (which can go up until millions),
    so I think it is not a problem here.

    Bart
    Bart Friederichs Guest

Similar Threads

  1. Replies: 1
    Last Post: January 23rd, 12:06 PM
  2. mysql load_file() function
    By Ben-Nes Yonatan in forum PostgreSQL / PGSQL
    Replies: 12
    Last Post: February 8th, 01:09 PM
  3. mysql with php 4.3.4 - function not present??
    By Vorxion in forum PHP Development
    Replies: 2
    Last Post: December 13th, 08:26 AM
  4. Need hep with mysql function
    By Zlut Arch in forum PHP Development
    Replies: 1
    Last Post: July 11th, 01:45 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