**Table 13.12 Mathematical Functions**

Name | Description |
---|---|

`ABS()` | Return the absolute value |

`ACOS()` | Return the arc cosine |

`ASIN()` | Return the arc sine |

`ATAN()` | Return the arc tangent |

`ATAN2()` , `ATAN()` | Return the arc tangent of the two arguments |

`CEIL()` | Return the smallest integer value not less than the argument |

`CEILING()` | Return the smallest integer value not less than the argument |

`CONV()` | Convert numbers between different number bases |

`COS()` | Return the cosine |

`COT()` | Return the cotangent |

`CRC32()` | Compute a cyclic redundancy check value |

`DEGREES()` | Convert radians to degrees |

`EXP()` | Raise to the power of |

`FLOOR()` | Return the largest integer value not greater than the argument |

`LN()` | Return the natural logarithm of the argument |

`LOG()` | Return the natural logarithm of the first argument |

`LOG10()` | Return the base-10 logarithm of the argument |

`LOG2()` | Return the base-2 logarithm of the argument |

`MOD()` | Return the remainder |

`PI()` | Return the value of pi |

`POW()` | Return the argument raised to the specified power |

`POWER()` | Return the argument raised to the specified power |

`RADIANS()` | Return argument converted to radians |

`RAND()` | Return a random floating-point value |

`ROUND()` | Round the argument |

`SIGN()` | Return the sign of the argument |

`SIN()` | Return the sine of the argument |

`SQRT()` | Return the square root of the argument |

`TAN()` | Return the tangent of the argument |

`TRUNCATE()` | Truncate to specified number of decimal places |

All mathematical functions return `NULL`

in the
event of an error.

Returns the absolute value of

.`X`

mysql>

-> 2 mysql>`SELECT ABS(2);`

-> 32`SELECT ABS(-32);`

This function is safe to use with

`BIGINT`

values.Returns the arc cosine of

, that is, the value whose cosine is`X`

. Returns`X`

`NULL`

ifis not in the range`X`

`-1`

to`1`

.mysql>

-> 0 mysql>`SELECT ACOS(1);`

-> NULL mysql>`SELECT ACOS(1.0001);`

-> 1.5707963267949`SELECT ACOS(0);`

Returns the arc sine of

, that is, the value whose sine is`X`

. Returns`X`

`NULL`

ifis not in the range`X`

`-1`

to`1`

.mysql>

-> 0.20135792079033 mysql>`SELECT ASIN(0.2);`

+-------------+ | ASIN('foo') | +-------------+ | 0 | +-------------+ 1 row in set, 1 warning (0.00 sec) mysql>`SELECT ASIN('foo');`

+---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' | +---------+------+-----------------------------------------+`SHOW WARNINGS;`

Returns the arc tangent of

, that is, the value whose tangent is`X`

.`X`

mysql>

-> 1.1071487177941 mysql>`SELECT ATAN(2);`

-> -1.1071487177941`SELECT ATAN(-2);`

Returns the arc tangent of the two variables

and`X`

. It is similar to calculating the arc tangent of`Y`

, except that the signs of both arguments are used to determine the quadrant of the result./`Y`

`X`

mysql>

-> -0.78539816339745 mysql>`SELECT ATAN(-2,2);`

-> 1.5707963267949`SELECT ATAN2(PI(),0);`

Returns the smallest integer value not less than

.`X`

mysql>

-> 2 mysql>`SELECT CEILING(1.23);`

-> -1`SELECT CEILING(-1.23);`

For exact-value numeric arguments, the return value has an exact-value numeric type. For string or floating-point arguments, the return value has a floating-point type.

Converts numbers between different number bases. Returns a string representation of the number

, converted from base`N`

to base`from_base`

. Returns`to_base`

`NULL`

if any argument is`NULL`

. The argumentis interpreted as an integer, but may be specified as an integer or a string. The minimum base is`N`

`2`

and the maximum base is`36`

. Ifis a negative number,`from_base`

is regarded as a signed number. Otherwise,`N`

is treated as unsigned.`N`

`CONV()`

works with 64-bit precision.mysql>

-> '1010' mysql>`SELECT CONV('a',16,2);`

-> '172' mysql>`SELECT CONV('6E',18,8);`

-> '-H' mysql>`SELECT CONV(-17,10,-18);`

-> '40'`SELECT CONV(10+'10'+'10'+X'0a',10,10);`

Returns the cosine of

, where`X`

is given in radians.`X`

mysql>

-> -1`SELECT COS(PI());`

Returns the cotangent of

.`X`

mysql>

-> -1.5726734063977 mysql>`SELECT COT(12);`

-> NULL`SELECT COT(0);`

Computes a cyclic redundancy check value and returns a 32-bit unsigned value. The result is

`NULL`

if the argument is`NULL`

. The argument is expected to be a string and (if possible) is treated as one if it is not.mysql>

-> 3259397556 mysql>`SELECT CRC32('MySQL');`

-> 2501908538`SELECT CRC32('mysql');`

Returns the argument

, converted from radians to degrees.`X`

mysql>

-> 180 mysql>`SELECT DEGREES(PI());`

-> 90`SELECT DEGREES(PI() / 2);`

Returns the value of

*e*(the base of natural logarithms) raised to the power of. The inverse of this function is`X`

`LOG()`

(using a single argument only) or`LN()`

.mysql>

-> 7.3890560989307 mysql>`SELECT EXP(2);`

-> 0.13533528323661 mysql>`SELECT EXP(-2);`

-> 1`SELECT EXP(0);`

Returns the largest integer value not greater than

.`X`

mysql>

-> 1, -2`SELECT FLOOR(1.23), FLOOR(-1.23);`

For exact-value numeric arguments, the return value has an exact-value numeric type. For string or floating-point arguments, the return value has a floating-point type.

Formats the number

to a format like`X`

`'#,###,###.##'`

, rounded todecimal places, and returns the result as a string. For details, see Section 13.5, “String Functions”.`D`

This function can be used to obtain a hexadecimal representation of a decimal number or a string; the manner in which it does so varies according to the argument's type. See this function's description in Section 13.5, “String Functions”, for details.

Returns the natural logarithm of

; that is, the base-`X`

*e*logarithm of. If`X`

is less than or equal to 0.0E0, the function returns`X`

`NULL`

and (as of MySQL 5.7.4) a warning “Invalid argument for logarithm” is reported.mysql>

-> 0.69314718055995 mysql>`SELECT LN(2);`

-> NULL`SELECT LN(-2);`

This function is synonymous with

`LOG(`

. The inverse of this function is the)`X`

`EXP()`

function.If called with one parameter, this function returns the natural logarithm of

. If`X`

is less than or equal to 0.0E0, the function returns`X`

`NULL`

and (as of MySQL 5.7.4) a warning “Invalid argument for logarithm” is reported.The inverse of this function (when called with a single argument) is the

`EXP()`

function.mysql>

-> 0.69314718055995 mysql>`SELECT LOG(2);`

-> NULL`SELECT LOG(-2);`

If called with two parameters, this function returns the logarithm of

to the base`X`

. If`B`

is less than or equal to 0, or if`X`

is less than or equal to 1, then`B`

`NULL`

is returned.mysql>

-> 16 mysql>`SELECT LOG(2,65536);`

-> 2 mysql>`SELECT LOG(10,100);`

-> NULL`SELECT LOG(1,100);`

`LOG(`

is equivalent to,`B`

)`X`

`LOG(`

.) / LOG(`X`

)`B`

Returns the base-2 logarithm of

. If`X`

is less than or equal to 0.0E0, the function returns`X`

`NULL`

and (as of MySQL 5.7.4) a warning “Invalid argument for logarithm” is reported.mysql>

-> 16 mysql>`SELECT LOG2(65536);`

-> NULL`SELECT LOG2(-100);`

`LOG2()`

is useful for finding out how many bits a number requires for storage. This function is equivalent to the expression`LOG(`

.) / LOG(2)`X`

Returns the base-10 logarithm of

. If`X`

is less than or equal to 0.0E0, the function returns`X`

`NULL`

and (as of MySQL 5.7.4) a warning “Invalid argument for logarithm” is reported.mysql>

-> 0.30102999566398 mysql>`SELECT LOG10(2);`

-> 2 mysql>`SELECT LOG10(100);`

-> NULL`SELECT LOG10(-100);`

Modulo operation. Returns the remainder of

divided by`N`

.`M`

mysql>

-> 4 mysql>`SELECT MOD(234, 10);`

-> 1 mysql>`SELECT 253 % 7;`

-> 2 mysql>`SELECT MOD(29,9);`

-> 2`SELECT 29 MOD 9;`

This function is safe to use with

`BIGINT`

values.`MOD()`

also works on values that have a fractional part and returns the exact remainder after division:mysql>

-> 1.5`SELECT MOD(34.5,3);`

`MOD(`

returns,0)`N`

`NULL`

.Returns the value of π (pi). The default number of decimal places displayed is seven, but MySQL uses the full double-precision value internally.

mysql>

-> 3.141593 mysql>`SELECT PI();`

-> 3.141592653589793116`SELECT PI()+0.000000000000000000;`

Returns the value of

raised to the power of`X`

.`Y`

mysql>

-> 4 mysql>`SELECT POW(2,2);`

-> 0.25`SELECT POW(2,-2);`

This is a synonym for

`POW()`

.Returns the argument

, converted from degrees to radians. (Note that π radians equals 180 degrees.)`X`

mysql>

-> 1.5707963267949`SELECT RADIANS(90);`

Returns a random floating-point value

in the range`v`

`0`

<=<`v`

`1.0`

. If a constant integer argumentis specified, it is used as the seed value, which produces a repeatable sequence of column values. In the following example, note that the sequences of values produced by`N`

`RAND(3)`

is the same both places where it occurs.mysql>

Query OK, 0 rows affected (0.42 sec) mysql>`CREATE TABLE t (i INT);`

Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql>`INSERT INTO t VALUES(1),(2),(3);`

+------+------------------+ | i | RAND() | +------+------------------+ | 1 | 0.61914388706828 | | 2 | 0.93845168309142 | | 3 | 0.83482678498591 | +------+------------------+ 3 rows in set (0.00 sec) mysql>`SELECT i, RAND() FROM t;`

+------+------------------+ | i | RAND(3) | +------+------------------+ | 1 | 0.90576975597606 | | 2 | 0.37307905813035 | | 3 | 0.14808605345719 | +------+------------------+ 3 rows in set (0.00 sec) mysql>`SELECT i, RAND(3) FROM t;`

+------+------------------+ | i | RAND() | +------+------------------+ | 1 | 0.35877890638893 | | 2 | 0.28941420772058 | | 3 | 0.37073435016976 | +------+------------------+ 3 rows in set (0.00 sec) mysql>`SELECT i, RAND() FROM t;`

+------+------------------+ | i | RAND(3) | +------+------------------+ | 1 | 0.90576975597606 | | 2 | 0.37307905813035 | | 3 | 0.14808605345719 | +------+------------------+ 3 rows in set (0.01 sec)`SELECT i, RAND(3) FROM t;`

With a constant initializer, the seed is initialized once when the statement is compiled, prior to execution. If a nonconstant initializer (such as a column name) is used as the argument, the seed is initialized with the value for each invocation of

`RAND()`

. (One implication of this is that for equal argument values,`RAND()`

will return the same value each time.)To obtain a random integer

in the range`R`

<=`i`

<`R`

, use the expression`j`

`FLOOR(`

−+ RAND() * (`i`

`j`

. For example, to obtain a random integer in the range the range))`i`

`7`

<=<`R`

`12`

, you could use the following statement:SELECT FLOOR(7 + (RAND() * 5));

`RAND()`

in a`WHERE`

clause is re-evaluated every time the`WHERE`

is executed.Use of a column with

`RAND()`

values in an`ORDER BY`

or`GROUP BY`

clause may yield unexpected results because for either clause a`RAND()`

expression can be evaluated multiple times for the same row, each time returning a different result. However, you can retrieve rows in random order like this:mysql>

`SELECT * FROM`

ORDER BY RAND();`tbl_name`

`ORDER BY RAND()`

combined with`LIMIT`

is useful for selecting a random sample from a set of rows:mysql>

`SELECT * FROM table1, table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000;`

`RAND()`

is not meant to be a perfect random generator. It is a fast way to generate random numbers on demand that is portable between platforms for the same MySQL version.This function is unsafe for statement-based replication. A warning is logged if you use this function when

`binlog_format`

is set to`STATEMENT`

. (Bug #49222)Rounds the argument

to`X`

decimal places. The rounding algorithm depends on the data type of`D`

.`X`

defaults to 0 if not specified.`D`

can be negative to cause`D`

digits left of the decimal point of the value`D`

to become zero.`X`

mysql>

-> -1 mysql>`SELECT ROUND(-1.23);`

-> -2 mysql>`SELECT ROUND(-1.58);`

-> 2 mysql>`SELECT ROUND(1.58);`

-> 1.3 mysql>`SELECT ROUND(1.298, 1);`

-> 1 mysql>`SELECT ROUND(1.298, 0);`

-> 20`SELECT ROUND(23.298, -1);`

The return type is the same type as that of the first argument (assuming that it is integer, double, or decimal). This means that for an integer argument, the result is an integer (no decimal places):

mysql>

+------------------+--------------+ | ROUND(150.000,2) | ROUND(150,2) | +------------------+--------------+ | 150.00 | 150 | +------------------+--------------+`SELECT ROUND(150.000,2), ROUND(150,2);`

`ROUND()`

uses the following rules depending on the type of the first argument:For exact-value numbers,

`ROUND()`

uses the “round half away from zero” or “round toward nearest” rule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.For approximate-value numbers, the result depends on the C library. On many systems, this means that

`ROUND()`

uses the "round to nearest even" rule: A value with any fractional part is rounded to the nearest even integer.

The following example shows how rounding differs for exact and approximate values:

mysql>

+------------+--------------+ | ROUND(2.5) | ROUND(25E-1) | +------------+--------------+ | 3 | 2 | +------------+--------------+`SELECT ROUND(2.5), ROUND(25E-1);`

For more information, see Section 13.21, “Precision Math”.

Returns the sign of the argument as

`-1`

,`0`

, or`1`

, depending on whetheris negative, zero, or positive.`X`

mysql>

-> -1 mysql>`SELECT SIGN(-32);`

-> 0 mysql>`SELECT SIGN(0);`

-> 1`SELECT SIGN(234);`

Returns the sine of

, where`X`

is given in radians.`X`

mysql>

-> 1.2246063538224e-16 mysql>`SELECT SIN(PI());`

-> 0`SELECT ROUND(SIN(PI()));`

Returns the square root of a nonnegative number

.`X`

mysql>

-> 2 mysql>`SELECT SQRT(4);`

-> 4.4721359549996 mysql>`SELECT SQRT(20);`

-> NULL`SELECT SQRT(-16);`

Returns the tangent of

, where`X`

is given in radians.`X`

mysql>

-> -1.2246063538224e-16 mysql>`SELECT TAN(PI());`

-> 1.5574077246549`SELECT TAN(PI()+1);`

Returns the number

, truncated to`X`

decimal places. If`D`

is`D`

`0`

, the result has no decimal point or fractional part.can be negative to cause`D`

digits left of the decimal point of the value`D`

to become zero.`X`

mysql>

-> 1.2 mysql>`SELECT TRUNCATE(1.223,1);`

-> 1.9 mysql>`SELECT TRUNCATE(1.999,1);`

-> 1 mysql>`SELECT TRUNCATE(1.999,0);`

-> -1.9 mysql>`SELECT TRUNCATE(-1.999,1);`

-> 100 mysql>`SELECT TRUNCATE(122,-2);`

-> 1028`SELECT TRUNCATE(10.28*100,0);`

All numbers are rounded toward zero.

randomly but ALSO use LIMIT so he could page

results - of course random will be different each time.

He wanted a random order that was not random for

the same session; so here is the idea:

In the web-side code calculate a numeric value which

is likely to stay the same for a session, perhaps

based on some session id, or timed-expiring cookie

value, etc, or from short-term stable HTTP headers.

Also require a numeric and well distributed value for

each record (doesn't have to be unique but works

well if it is).

Then:

... order by rand(numeric_field + session_value)

LIMIT blah;

So we see the ordering is preserved as

numeric_field+session_value will be the same for a

session, and numeric_field + session value are NOT

the same from row to row so we still get random

ordering.

Sam Liddicott

Example

in the WHERE clause you may have to do something like this

oem.oem_id=substring(sku,5,3)

Here sku is a string who substring starting from location 5 from left and then having total length of 3 is compared with a numeric value of oem_id to satisfy the WHERE clause.

For more details see

http://www.bitmechanic.com/mail-archives/mysql/May1997/0494.html

select round((((cost*100) - (cost*100)%5) /100), 2) from SessionCost;

num = the number you are rounding

ROUND( TRUNCATE(num,2) + REPLACE( ( (num*1000) - ( TRUNCATE(num,2) *1000) / 1000, '5', '6'), 2)

This example rounds to 2 decimal places. If you want to round to three decimals just switch out the 2s for 3s and the 1000s for 10000s, etc.

It basically works by replacing all the fives beyond the two decimal places with sixes, which will always round up. Then calling the round function.

floor(num * 100 + .55)/100

In a list where some elements had priority and others not I needed to randomise the prioritised items and not the rest. The prioritised entries all had a value of 1 in a field called 'enhanced' and all entries had an abbreviated name ('abbrev') that they were otherwise sorted by. Using

ORDER BY (RAND() * enhanced) desc, abbrev

I could change the order of the enhanced listings yet maintain an alphabetical listing thereafter.

As mentioned in the manual, ROUND function has problems with values near to the limit values. The same prblem is found in the format function Let's see it:

round(1.15,1)=1.2 OK

round(1.25,1)=1.2 BAD, sould be 1.3

round(1.35,1)=1.4 OK

round(1.45,1)=1.4 BAD, sould be 1.5

round(1.55,1)=1.6 OK

And so on...

A walkarround for this sould be to use truncate adding 0,06. The same problem in found in the format function.

Of corse, if you want to use more than one digit, you should add as many 0 as you need to de value added in the truncate function. Note that in case of using 2 digits, the result of format is correct but round stills failing. It is more reliable to do the calculation using your own formula, with truncate.

Take special care when using the the unsafe version with grouping functions like SUM(), as the end result can be way off if there is a big mix of negative/positive numbers.

sign(num) * truncate(abs(num)+0.06,1)

E.g...

Most of us consider this:

25.725 to be 25 dollars and 73 cents

But mysql was returning: round(25.725,2) as 25.72 which was throwing off my calc.

So, my workaround after not finding a solution is:

if num=25.725

============================

truncate(num + 0.0051,2)

============================

will yield this result: 25.73, which is correct.

I hope this helps someone else.

Ken

If you use 6 as the number you are adding to the digit beyond significance then you will be rounding up 0.4s as well as 0.5s.

Here is my method:

rounding to two decimals

TRUNCATE(num + (SIGN(num) * 0.005), 2)

example 1

TRUNCATE(0.004 + (SIGN(0.004) * 0.005),2) = TRUNCATE(0.009,2) = 0.00

example 2

TRUNCATE(0.005 + (SIGN(0.005) * 0.005),2) = TRUNCATE(0.010,2) = 0.01

for three decimals it would be

TRUNCATE(num + (SIGN(num) * 0.0005), 3)

etc.

BTW this seems to be how PHP's round function works, so if you are trying to get calculations in PHP to match MySQL this is how I did it.

Given what is printed here:

FLOOR(i + RAND() * (j - i))

I only ever get results in the range of i to j-1.

Shouldn't it be

FLOOR(i + RAND() * (j - i + 1 )) ?

I am getting results in the range I need with that. Maybe I am missing something, maybe once in a great while there will be a result that is j+1 and I have just not seen it.

BTW, I am using it as:

CREATE FUNCTION IRAND(param1 INT, param2 INT) RETURNS INT

RETURN FLOOR(param1 + RAND() * (param2-param1+1)) ;

Thus, the values '0.0' and '1.0' can be returned althoug the changes are very very little.

In the example, where one wants a value between 7 and 12 inclusive, the value of '12' will hardly ever be returned.

I wanted a value of '0' or '1' (i.e. yes or no), so I used FLOOR(RAND() + 0.5), cuz if I'd used FLOOR(i + RAND() * (j – i), i.e. FLOOR(0 + RAND() * (1 – 0)) which evaluates to (FLOOR(RAND()), I would have gotten only one '1' and a trillillizillion 0's.

ok i experienced like the description says different behaviour on rounding on different systems

so based on the examples by other ppl who might work for their issue but are neither save nor a

general purpose solution i have come up with my own solution for rounding up on 5

the number of decimal places you want : X

number : Y

general solution :

TRUNCATE((Y+SIGN(Y)*(POW(10,(1-X))/18)),X)

example (the other solutions fail here) :

y = 12.449

x = 1

result : 12.5

hope this helps you too

CREATE FUNCTION `v_round`(round_me DOUBLE)

RETURNS decimal(10,2)

DETERMINISTIC

SQL SECURITY DEFINER

COMMENT ''

BEGIN

return round_me;

END;

To quickly select a random row, basically, do it in two SELECTS:

1. first SELECT finds out number of rows available, usnig a WHERE clause if desired.

2. web code chooses a random row from the number of rows (from step 1.) and saves this number in $x.

3. second SELECT (using the same WHERE clause in step 1.) uses LIMIT 1,$x.

In avarage it means reading through <NumberOfRecords>/2 records every time, thus it's slow.

Here's some Ruby:

max = dbh.query("select max(id) from table").fetch_row.first

rand_id = rand(max)

row = dbh.query("select * from table where id = #{rand_id}").fetch_hash

puts "Fetched: #{row['id']}"

Doing it that way doesn't work for everyone, some id values less than the max id might no longer exist in the table due to deletes.

Here's a simple solution (in php):

mysql_query('START TRANSACTION');

$count=mysql_fetch_row(mysql_query('SELECT COUNT(*) FROM table'));

$randomRow=mysql_fetch_row(mysql_query('SELECT * FROM table LIMIT '.(mt_rand(0,$count[0]-1)).',1'));

mysql_query('ROLLBACK');

Haven't really tested it, but you'd get the idea.

It's similar to michael's idea (posted above), only he had the limit parameters in the wrong order.

SELECT MAX(pkey) FROM articles;

...then grab a random number (shown as $r below) between 1 and max in your code. Now return back to SQL like so:

SELECT * FROM articles where pkey > $r LIMIT $limit;

...where $limit is the number of rows you want to likely return.

...Then, to create the illusion of more randomness, just use an ORDER BY clause on the second SELECT above based on something arbitrary. For instance, if 'articles' has a column like author name and another like category, you could change the SELECT statement above like:

SELECT * FROM articles where pkey > $r ORDER BY category, name LIMIT $limit;

...So, by using this strategy, it's faster than having to randomly determine your pkeys and selecting only one record at a time.

In my case, I wanted to sort classified listings with some close approximation of randomness in order to rotate the listings, and this strategy has worked for me.

CREATE FUNCTION ROUND_COMMERCIAL(value DOUBLE, preci INT(11)) RETURNS DOUBLE NO SQL

RETURN TRUNCATE((value * POW(10, preci)) + (IF(value = 0, 1,(value / ABS(value)))*(0.5 * POW(1, preci*-1))), 0) / POW(10, preci);

SET @toGet=10;

SET @left=(SELECT COUNT(*) FROM tableName)+1;

SELECT *, @toGet:=@toGet-1

FROM tableName

WHERE (@left:=@left-1)>0 AND RAND()<@toGet/@left;

It's much faster than ORDER BY RAND() LIMIT 10 (especially if you want to fetch small random subset of rows stored in table) but if it happens to return same set of rows, it returns them always in same order. If you want them to have random order then you have to scramble them after fetching using subquery:

SET @toGet=10;

SET @left=(SELECT COUNT(*) FROM tableName)+1;

SELECT * FROM (

SELECT *, @toGet:=@toGet-1

FROM tableName

WHERE (@left:=@left-1)>0 AND RAND()<@toGet/@left

) t ORDER BY RAND()

or on client side.

mysql> select version(),if(2.268>1.249,truncate(ceil(((2.268-1.249)/6+.01)*100)/100,2),'0.00') as fscpm;

mysql> select version(),truncate(if(2.268>1.249,ceil(((2.268-1.249)/6+.01)*100)/100,'0.00'),2) as fscpm;

CREATE DEFINER=`root`@`%` FUNCTION `BROUND`( value DECIMAL(65,30), places TINYINT(3) UNSIGNED ) RETURNS decimal(65,30) COMMENT 'WARNING over decimal(65,30) will round normally!'

DETERMINISTIC

RETURN

CASE WHEN

LOCATE( '.', value ) >= 1

AND LENGTH( SUBSTRING( value, LOCATE( '.', value ) +1 ) ) < 31

AND places > -1

AND LENGTH( value ) - LOCATE( '.', value ) > places

AND SUBSTRING( value, LOCATE( '.', value ) + places + 1, 1 ) = 5

AND SUBSTRING( value, LOCATE( '.', value ) + places + 2 ) = 0

AND SUBSTRING( value, LOCATE( '.', value ) + places + (CASE WHEN places = 0 THEN -1 ELSE 0 END ), 1 ) % 2 = 1

THEN

SUBSTRING( value, 1, LOCATE( '.', value ) + places + (CASE WHEN places = 0 THEN -1 ELSE 0 END ) )

ELSE

ROUND( value, places )

END;

WARNINGS:

- The old function I had posted here before today was wrong, to anyone who used it I am deeply sorry.

- Also do not use Felipe's function below as it is broken because a correct BROUND(6.434503,3) function should indeed return 6.435, NOT 6.434, as there is a 3 to the right of the 5. However BROUND(6.434500,3) WILL return 6.434. In banker's rounding, the only difference between regular rounding occurs when what is being rounded either ends in a 5, or ends in a 5 with a few zeroes after it. If however you do want this incorrect behaviour for some reason, you can remove "AND SUBSTRING( value, LOCATE( '.', value ) + places + 2 ) = 0" from this or use Felipe's function instead.

- Note that if you pass a value greater than 29 into my second parameter you will get regular rounding, because the DECIMAL data type has a precision of 30, and to pass anything larger than 29 in the second parameter would mean you would have gone over that limit. To avoid getting a fatal error when doing this, and for simplicity, I used strings in this function, and made it fail over to ROUND() in this instance.

- Do your own testing first of course

CREATE FUNCTION BankersRound(Val DECIMAL(32,16), Digits INT)

RETURNS DECIMAL(32,16)

RETURN

IF(ABS(Val - ROUND(Val, Digits)) * POWER(10, Digits+1) = 5,

IF(CONVERT(TRUNCATE(ABS(Val) * POWER(10,Digits), 0),UNSIGNED) % 2,

ROUND(Val,Digits),

TRUNCATE(Val,Digits)

),

ROUND(Val, Digits)

);

The input test was

select BankersRound(1.346,3),BankersRound(4.735500,3),BankersRound(7.834500,3),BankersRound(2.983600,3),BankersRound(6.434503,3);

and the expected result

1.346 4.736 7.834 2.984 6.435

and i got

1.3460000000000000, 4.7360000000000000, 7.8340000000000000, 2.9840000000000000, 6.4350000000000000

on MySQL 5.1.42

select BankersRound(6.434503,3);

returns

6.4350000000000000

as there is a 3 to the right of the 5. So if can you help me to see why I'm wrong?

PS: Sorry for inconvenience, I don't want to be rude with you. For common purposes, for instace, if you have a decimal column (my case) you can use my version. If I'm not wrong it's probably faster. Although if you need more precision or work with bigger numbers you can use Mike's version. Finally I don't think we need to fight for this. ;-)

Sorry for the beautiful English

SET @v = - 2.5;

SELECT @v - MOD( @v , 1 ) , FLOOR( @v )

You'll gain:

@v-MOD(@v,1) FLOOR(@v)

-2.000000000000000000000000000000 -3

In order words I needed to round up or down to the nearest 5 cents.

It took me some time to work it out so I thought I would document it here in order to save somebody else the hair loss.

The basic formula was:-

ROUND( value / 5, 2 ) * 5.

My calc was a little more complex ...

ROUND( (v1 + ( v2 / 3 ) ) / 5, 2 ) * 5.

Some test result are:-

I was then asked to round everything UP to the nearest 5 cents.

The formula for this is:-

ROUND( ( ( v1 + ( v2 / 3 ) + 0.03 ) ) / 5, 2 ) * 5

I do hope this helps somebody.

SET @N:= CAST(SHA1(RAND()) AS CHAR);

SELECT @N, CONV(@N, 16, 10), ~0 as max_bigint_unsigned;

1 row in set (0.00 sec)