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.




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.

Example: FORMAT() function


SELECT FORMAT(12324.2573, 3);


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

Sample 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


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


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.

Sample 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)

PHP script:

<!doctype html>
<html lang="en">
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-mathematical-format - php mysql exmples | w3resource</title>
<meta name="description" content="example-mathematical-format - php mysql exmples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>A list of books and their prices. Only books which are priced more than 150 are listed and prices are formatted upto four decimal places:</h2>
<table class='table table-bordered'>
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT book_name,FORMAT(book_price,4)
FROM book_mast
WHERE book_price>150') as $row) {
echo "<tr>";
echo "<td>" . $row['book_name'] . "</td>";
echo "<td>" . $row['FORMAT(book_price,4)'] . "</td>";
echo "</tr>"; 


View the example in browser

Online Practice Editor:

All Mathematical Functions

MySQL Mathematical Functions, slide presentation

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

Follow us on Facebook and Twitter for latest update.

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook