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:

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