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)
Previous: TRUNCATE()
Next: MySQL date and time functions
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics