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

+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| book_id | book_name                           | isbn_no     | cate_id | aut_id | pub_id | dt_of_pub  | pub_lang | no_page | book_price |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| BK001   | Introduction to Electrodynamics     | 0000979001  | CA001   | AUT001 | P003   | 2001-05-08 | English  |     201 |      85.00 |
| BK002   | Understanding of Steel Construction | 0000979002  | CA002   | AUT002 | P001   | 2003-07-15 | English  |     300 |     105.50 |
| BK003   | Guide to Networking                 | 0000979003  | CA003   | AUT003 | P002   | 2002-09-10 | Hindi    |     510 |     200.00 |
| BK004   | Transfer  of Heat and Mass          | 0000979004  | CA002   | AUT004 | P004   | 2004-02-16 | English  |     600 |     250.00 |
| BK005   | Conceptual Physics                  | 0000979005  | CA001   | AUT005 | P006   | 2003-07-16 | NULL     |     345 |     145.00 |
| BK006   | Fundamentals of Heat                | 0000979006  | CA001   | AUT006 | P005   | 2003-08-10 | German   |     247 |     112.00 |
| BK007   | Advanced 3d Graphics                | 0000979007  | CA003   | AUT007 | P002   | 2004-02-16 | Hindi    |     165 |      56.00 |
| BK008   | Human Anatomy                       | 0000979008  | CA005   | AUT008 | P006   | 2001-05-17 | German   |      88 |      50.50 |
| BK009   | Mental Health Nursing               | 0000979009  | CA005   | AUT009 | P007   | 2004-02-10 | English  |     350 |     145.00 |
| BK010   | Fundamentals of Thermodynamics      | 0000979010  | CA002   | AUT010 | P007   | 2002-10-14 | English  |     400 |     225.00 |
... ... ...
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+

View the table

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

PREV : TRUNCATE()
NEXT : MySQL date and time functions