* Philipp Sutter > Am Die, 2003-01-21 um 15.03 schrieb Roger Baklund: > > * Philipp Sutter > > > I have a Mysql-table with a field of type double. when i retrieve > > > very small > > > numbers from this table I get them allways in a scientific notation: > > > i.e. 8.34e-05. what i have to do, that i get the same number in normal > > > notation: 0.0000834? > > > > See the FORMAT() function: > > > > <URL: http://www.mysql.com/doc/en/Miscellaneous_functions.html > > > > > -- > > Roger > > format() has two disadvantages for me: > 1) I do not want, that number will be rounded.
hm... then you may already have a problem, because float/double are approximate number types. You do not control the exact value: mysql> create table floattest (f float); Query OK, 0 rows affected (0.01 sec) mysql> insert floattest set f=0.000001; Query OK, 1 row affected (0.00 sec) mysql> select f,format(f,6),format(f,14),format(f,15) from floattest; +--------+-------------+------------------+-------------------+ | f | format(f,6) | format(f,14) | format(f,15) | +--------+-------------+------------------+-------------------+ | 1e-006 | 0.000001 | 0.00000100000000 | 0.000000999999997 | +--------+-------------+------------------+-------------------+ 1 row in set (0.00 sec) These results may vary between different computers, depending on the underlaying C library used for floating point calculations. > 2) when I try: format(number,13), I will get i.e. 0.0000834000000; and I > do not want the ending zeros. > > I there an other way to get only i.e. 0.0000834? You could try using DECIMAL, which is an exact data type, but many of the same problems apply. For instance, arithmetics and expressions like " SET f1 = 1.05-f2" or "WHERE field = 1.234" or similar can not be safely used, because approximate numbers are used for any constant expressions, even if the column type is DECIMAL (or NUMERIC). I never use FLOAT/REAL/NUMERIC/DECIMAL myself, mainly to avoid these 'approximity-problems'. If I need a column with for instance monetary data, I just multiply by 100 when I store the value, and divide by hundred when I retrieve: mysql> create table prices (item int,price int); Query OK, 0 rows affected (0.05 sec) mysql> INSERT prices SET item=123,price=14550; # 145.50 * 100 Query OK, 1 row affected (0.00 sec) mysql> SELECT item,price/100 price FROM prices; +------+--------+ | item | price | +------+--------+ | 123 | 145.50 | +------+--------+ 1 row in set (0.00 sec) A totally different approach may be to strip the trailing zeroes from the returned value, using whatever scripting language you are using for your application. That is, if about 14 digits is exact enough for your needs. I also tried some hacks, just for the fun of it, but it did not work out in my 3.23.30. Maybe it will work in a newer version: mysql> select f,right(f,3)+0 from floattest; +--------+--------------+ | f | right(f,3)+0 | +--------+--------------+ | 1e-006 | 6 | +--------+--------------+ 1 row in set (0.00 sec) mysql> select f,right(f,3)+0,format(f,right(f,3)+0) from floattest; ERROR 1064: You have an error in your SQL syntax near 'right(f,3)+0) from floattest' at line 1 mysql> select f,@a:=right(f,3)+0,@a from floattest; +--------+------------------+------+ | f | @a:=right(f,3)+0 | @a | +--------+------------------+------+ | 1e-006 | 6 | 6 | +--------+------------------+------+ 1 row in set (0.00 sec) mysql> select f,@a:=right(f,3)+0,format(f,@a) from floattest; ERROR 1064: You have an error in your SQL syntax near '@a) from floattest' at line 1 (It seems as the FORMAT() function is picky about the second parameter, I guess it must be a constant.) HTH, -- Roger --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
