MySQL FORMAT() function

Last update on April 04 2022 13:10:02 (UTC/GMT +8 hours)

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.

Syntax:

FORMAT (N, D);

Argument:

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

Code:

SELECT FORMAT(12324.2573, 3);

Explanation:

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

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.

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">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<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">
</head>
<body>
<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'>
<tr>
<th>Books</th><th>Price</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password"; 
$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>"; 
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Online Practice Editor:

All Mathematical Functions

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