How can we retrieve the output having decimal values of a column in a specified format?

MySQLMySQLi Database

MySQL FORMAT() function, converts a number to a format like #,###,###.### which is rounded up to the number of decimal places specified and returns the result as a string, can be used to retrieve the output having decimal values of a column in a specified format. To understand it, we are taking an example of table ‘estimated_cost’ which have the following data −

mysql> Select * from estimated_cost;
+----+-----------------+-----------+---------------+
| Id | Name_Company    | Tender_id | Tender_value  |
+----+-----------------+-----------+---------------+
| 1  | ABC Ltd.        | 110       | 256.3256879   |
| 2  | Chd Ltd.        | 116       | 8569.25647879 |
| 3  | City group Ltd. | 202       | 23647.2365987 |
| 4  | Hjkl Ltd.       | 215       | 6598.327846   |
+----+-----------------+-----------+---------------+
4 rows in set (0.00 sec)

Now, suppose if we want to see the values of column ‘Tender_value’ up to some specified number of decimal places then it can be done with the help of the following query −

mysql> Select FORMAT(Tender_value,2) from estimated_cost;
+------------------------+
| FORMAT(Tender_value,2) |
+------------------------+
| 256.33                 |
| 8,569.26               |
| 23,647.24              |
| 6,598.33               |
+------------------------+
4 rows in set (0.00 sec)

The query above returns the value of Tender_value up to 2 decimal places.

mysql> Select FORMAT(Tender_value,1) from estimated_cost1;
+------------------------+
| FORMAT(Tender_value,1) |
+------------------------+
| 256.3                  |
| 8,569.3                |
| 23,647.2               |
| 6,598.3                |
+------------------------+
4 rows in set (0.00 sec)

The query above returns the value of Tender_value up to 1 decimal place.

raja
Published on 21-Feb-2018 15:48:59
Advertisements