MySQL FORMAT() function
In this context, we will learn how we can use the MySQL FORMAT() function with proper syntax and good examples.
Introduction of MySQL FORMAT() function
This function in MySQL helps to format the given number like '#, ###, ###.##", round them to certain decimal points, and returns the result in the form of a string.
Syntax of the MySQL FORMAT() function
The syntax of the MySQL FORMAT() function is given as follows:
FORMAT(M, DEC, locale)
Parameters or arguments used in MySQL FORMAT() function:
There are three parameters accepted by the FORMAT() function in MySQL, which are given as follows:
1. M –
The number that is to be formatted.
2. DEC –
This is the number of decimal places to which the number is rounded off.
3. locale –
It's an optional parameter that decides a thousand separators and grouping between separators. By default, en_US locale is present in MySQL.
Returns:
The function will return the given number with proper format, round it off to a certain decimal place, and return the number in the form of a string.
Applications used for FORMAT() function:
The FORMAT() function can be used in the given below MySQL versions.:
- MySQL 5.7
- MySQL 5.6
- MySQL 5.5
- MySQL 5.1
- MySQL 5.0
- MySQL 4.1
- MySQL 4.0
- MySQL 3.23
Examples of MySQL FORMAT() function:
Now we will look into some MySQL FORMAT() function examples and will explore how to use the FORMAT function in MySQL.
Example-1 :
In this example, we will round off the given number by using the FORMAT() function in MySQL to 2 decimal places.
SELECT FORMAT(555454.12365, 2) AS form;
Output :
form
555, 454.12
1 row in set (0.00 sec)
Example 2:
In this example, we will round off the given number by using MySQL's FORMAT() function with 0 decimal places.
SELECT FORMAT(130919999.456, 0)
AS form;
Output :
form
130, 919, 999
1 row in set (0.00 sec)
Example-3 :
Now we will Replace the en_US locale with the de_D locale.
SELECT FORMAT(27112020.1052, 3, 'de_DE') As form;
Output :
form
27.112.020, 105
1 row in set (0.00 sec)
Example-4 :
When we require to round off the column data, then we can take the help of the FORMAT() function in MySQL.
CREATE TABLE Commodity(
Commodity_Id INT AUTO_INCREMENT,
Commodity_Name VARCHAR(100) NOT NULL,
Price INT NOT NULL,
PRIMARY KEY(Commodity_Id )
);
Inserting values into the table:
INSERT INTO Commodity(Commodity_Name, Price)
VALUES
('MotorolaMobile', 75000.999 ),
('SmartWatch,' 73000.455 ),
('Camera,' 170000.545 ) ;
The table will look as follows.
SELECT * FROM Commodity;
| Commodity_Id | Commodity_Name | Price |
| 1 | MotorolaMobile | 75000.999 |
| 2 | Smartwatch | 73000.455 |
| 3 | Camera | 170000.545 |
Now we will format the Price column by rounding up to 1 decimal place.
SELECT
Commodity_Name, FORMAT(Price, 1) As New_price
FROM
Commodity;
Output:
| Commodity_Name | New_price |
| MotorolaMobile | 75, 001.0 |
| Smartwatch | 73, 000.5 |
| Camera | 170, 000.5 |
Application of MySQL FORMAT() function:
This function is used to format the given number like '#, ###, ###.##", round them to specific decimal points, and returns the result in the form of a string.
Summary:
In the above context, we have learned how we can use the FORMAT() function in MySQL used to format given number like '#, ###, ###.##", round them to specific decimal points, and returns the result in the form of a string.