FORMAT()

Description

Formats the number X to a format like ‘#,###,###.##’, rounded to D decimal places, and returns the result as a string.

Syntax

  1. > FORMAT(X,D[,locale])

Arguments

ArgumentsDescription
XRequired. X is the number need to format. If X is NULL, the function returns NULL.
DRequired. D is the number of decimal places to round.
If D is 0, the result has no decimal point or fractional part.
If D is NULL, the function returns NULL.
[,locale]Optional. The optional parameter enables a locale to be specified to be used for the result number’s decimal point, thousands separator, and grouping between separators. If the locale is NULL or not specified, the default locale is ‘en_US’. [,locale] supports locale parameters consistent with MySQL; see MySQL Server Locale Support.

Examples

  1. mysql> SELECT FORMAT(12332.123456, 4);
  2. +-------------------------+
  3. | format(12332.123456, 4) |
  4. +-------------------------+
  5. | 12,332.1235             |
  6. +-------------------------+
  7. 1 row in set (0.01 sec)
  9. mysql> SELECT FORMAT(12332.1,4);
  10. +--------------------+
  11. | format(12332.1, 4) |
  12. +--------------------+
  13. | 12,332.1000        |
  14. +--------------------+
  15. 1 row in set (0.00 sec)
  17. mysql> SELECT FORMAT(12332.2,0);
  18. +--------------------+
  19. | format(12332.2, 0) |
  20. +--------------------+
  21. | 12,332             |
  22. +--------------------+
  23. 1 row in set (0.00 sec)
  25. mysql> SELECT FORMAT(12332.2,2,'de_DE');
  26. +---------------------------+
  27. | format(12332.2, 2, de_DE) |
  28. +---------------------------+
  29. | 12.332,20                 |
  30. +---------------------------+
  31. 1 row in set (0.00 sec)
  33. mysql> SELECT FORMAT(19999999.999999999,4);
  34. +-------------------------------+
  35. | format(19999999.999999999, 4) |
  36. +-------------------------------+
  37. | 20,000,000.0000               |
  38. +-------------------------------+
  39. 1 row in set (0.01 sec)
  41. mysql> SELECT FORMAT("-.12334.2","2", "en_US");
  42. +-----------------------------+
  43. | format(-.12334.2, 2, en_US) |
  44. +-----------------------------+
  45. | -0.12                       |
  46. +-----------------------------+
  47. 1 row in set (0.00 sec)
  49. mysql> SELECT FORMAT("-.12334.2","2", "de_CH");
  50. +-----------------------------+
  51. | format(-.12334.2, 2, de_CH) |
  52. +-----------------------------+
  53. | -0.12                       |
  54. +-----------------------------+
  55. 1 row in set (0.01 sec)