On Wed, 27 Feb 2002 22:10:44 +0100, Roger Baklund wrote:
>One obvious workaround is to avoid floating point numbers... or in mysql
>terms, use the decimal/numeric data type instead of float/double... but that
>does not solve the problem in this case, I guess floating point is used
>internally in the round() function...?
>
>With different numbers in the test table, I discovered that my version also
>did the rounding using the id field, but it seems the decimals parameter of
>the round() function is evaluated only once, I can't get it to output a
>different number of decimals for each row:
>
>mysql> select *,round(num1,id) from tab4;
>+------+----------+----------------+
>| id | num1 | round(num1,id) |
>+------+----------+----------------+
>| 1 | 1.2345 | 1.2000 |
>| 2 | 12.3456 | 12.3500 |
>| 3 | 100.4235 | 100.4230 |
>+------+----------+----------------+
>3 rows in set (0.01 sec)
>
>Note that the number of digits after the decimal sign is always four, but
>the rightmost digits are just padding zeroes... the number of digits
>actually coming from the num1 field is determined by the id field, and the
>round() function "works", if you can accept .4235 -> .423 on _some_
>platforms... a little substring magic, and we got the desired result:
>
>mysql> select *,@a:=round(num1,id) as tmp,left(@a,locate('.',@a)+id)+0 as
>result from tab4;
>+------+----------+----------+---------+
>| id | num1 | tmp | result |
>+------+----------+----------+---------+
>| 1 | 1.2345 | 1.2000 | 1.2 |
>| 2 | 12.3456 | 12.3500 | 12.35 |
>| 3 | 100.4235 | 100.4230 | 100.423 |
>+------+----------+----------+---------+
>3 rows in set (0.00 sec)
>
>Putting some time & effort in it, I was able to work around the round()
>rounding error, but I must warn you, it is rather messy... ;o)
>
>mysql> select *,@a:=mid(number,1,floor(log10(number))+3+digits) as tmp,
> -> left(if(right(@a,1)+0>4,@a+pow(10,-(floor(log10(number))+1)),@a),
> -> length(@a)-1)+0 as result from testme;
>+----------+--------+----------+---------+
>| number | digits | tmp | result |
>+----------+--------+----------+---------+
>| 100.4235 | 3 | 100.4235 | 100.424 |
>| 85.4000 | 1 | 85.40 | 85.4 |
>+----------+--------+----------+---------+
>2 rows in set (0.00 sec)
>
>mysql> select *,@a:=mid(num1,1,floor(log10(num1))+3+id) as tmp,
> -> left(if(right(@a,1)+0>4,@a+pow(10,-(floor(log10(num1))+1)),@a),
> -> length(@a)-1)+0 as result from tab4;
>+------+----------+----------+---------+
>| id | num1 | tmp | result |
>+------+----------+----------+---------+
>| 1 | 1.2345 | 1.23 | 1.2 |
>| 2 | 12.3456 | 12.345 | 12.35 |
>| 3 | 100.4235 | 100.4235 | 100.424 |
>+------+----------+----------+---------+
>3 rows in set (0.00 sec)
>
>It's not a pretty sight, but it seems to do the work.
OK. No more caffeine for you! 8-))
Doug
>
>--
>Roger
>query
---------------------------------------------------------------------
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
