w3resource

MySQL FORMAT() function

FORMAT() function

MySQL FORMAT() returns the number N to a format like ‘#,###,###.##’ rounded to a number of decimal places and returns the result as a string. If there is no decimal point, decimal place is defined as 0.

This function is useful in -

  • By rounding to a specified number of decimal places, FORMAT() can help prevent the propagation of rounding errors that can occur in lengthy calculations.
  • FORMAT() improves the readability of large numbers by adding thousands separators, making it easier for users to quickly grasp the magnitude of the value.
  • In statistical analysis or data visualization, it's often beneficial to present results in a standardized format that's easy to interpret, which FORMAT() facilitates.
  • When generating reports or creating documentation, FORMAT() can be used to ensure that numerical data is presented in a professional and visually appealing manner.
  • The FORMAT() function enables you to adhere to locale-specific formatting requirements, ensuring consistency with local standards.
  • FORMAT() ensures that numbers which is a crucial of monetary in financial and accounting are presented in a standardized and easily interpretable manner.

Syntax:

FORMAT (N, D);

Arguments:

Name Description
N A number which is to be formatted up to D decimal places rounded up.
D A number indicating up to how many decimal places N will be rounded up.

MySQL Version: 8.0


Example: FORMAT() function

Code:

SELECT FORMAT(12324.2573, 3);

Explanation:

The above MySQL statement formats 12324.2573 up to 3 decimal rounded up.

Output:

mysql> SELECT FORMAT(12324.2573, 3);
+-----------------------+
| FORMAT(12324.2573, 3) |
+-----------------------+
| 12,324.257            | 
+-----------------------+
1 row in set (0.03 sec)

Example: FORMAT() function using table

Sample table: Book_mast


Code:

SELECT book_name,FORMAT(book_price,4)
      FROM book_mast            
      WHERE book_price>150;

Explanation:

The above MySQL statement returns those books from the book_mast table, whose price is more than 150 and returns the price rounded up to 4 decimal places.

Output:

mysql> SELECT book_name,FORMAT(book_price,4)
    ->       FROM book_mast            
    ->       WHERE book_price>150;
+--------------------------------+----------------------+
| book_name                      | FORMAT(book_price,4) |
+--------------------------------+----------------------+
| Guide to Networking            | 200.0000             | 
| Transfer  of Heat and Mass     | 250.0000             | 
| Fundamentals of Thermodynamics | 225.0000             | 
| Concepts in Health             | 180.0000             | 
+--------------------------------+----------------------+
4 rows in set (0.02 sec)

All Mathematical Functions

Previous: TRUNCATE()
Next: MySQL date and time functions



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/mysql/mathematical-functions/mysql-mathematical-format.php