round_bankers

description

Syntax

T round_bankers(T x[, d]) Rounds the argument x to d specified decimal places. d defaults to 0 if not specified. If d is negative, the left d digits of the decimal point are 0. If x or d is null, null is returned.

If d is a column, and x has Decimal type, scale of result Decimal will always be same with input Decimal.

  • If the rounding number is halfway between two numbers, the function uses banker’s rounding.
  • In other cases, the function rounds numbers to the nearest integer.

example

  1. mysql> select round_bankers(0.4);
  2. +--------------------+
  3. | round_bankers(0.4) |
  4. +--------------------+
  5. |                  0 |
  6. +--------------------+
  7. mysql> select round_bankers(-3.5);
  8. +---------------------+
  9. | round_bankers(-3.5) |
  10. +---------------------+
  11. |                  -4 |
  12. +---------------------+
  13. mysql> select round_bankers(-3.4);
  14. +---------------------+
  15. | round_bankers(-3.4) |
  16. +---------------------+
  17. |                  -3 |
  18. +---------------------+
  19. mysql> select round_bankers(10.755, 2);
  20. +--------------------------+
  21. | round_bankers(10.755, 2) |
  22. +--------------------------+
  23. |                    10.76 |
  24. +--------------------------+
  25. mysql> select round_bankers(1667.2725, 2);
  26. +-----------------------------+
  27. | round_bankers(1667.2725, 2) |
  28. +-----------------------------+
  29. |                     1667.27 |
  30. +-----------------------------+
  31. mysql> select round_bankers(1667.2725, -2);
  32. +------------------------------+
  33. | round_bankers(1667.2725, -2) |
  34. +------------------------------+
  35. |                         1700 |
  36. +------------------------------+
  37. mysql> SELECT number
  38.     -> , round_bankers(number * 2.5, number - 1) AS rb_decimal_column
  39.     -> , round_bankers(number * 2.5, 0) AS rb_decimal_literal
  40.     -> , round_bankers(cast(number * 2.5 AS DOUBLE), number - 1) AS rb_double_column
  41.     -> , round_bankers(cast(number * 2.5 AS DOUBLE), 0) AS rb_double_literal
  42.     -> FROM test_enhanced_round
  43.     -> WHERE rid = 1;
  44. +--------+-------------------+--------------------+------------------+-------------------+
  45. | number | rb_decimal_column | rb_decimal_literal | rb_double_column | rb_double_literal |
  46. +--------+-------------------+--------------------+------------------+-------------------+
  47. |      1 |               2.0 |                  2 |                2 |                 2 |
  48. +--------+-------------------+--------------------+------------------+-------------------+

keywords

  1. round_bankers