FORMAT() function in MySQL
FORMAT() :
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 :
FORMAT(N, D, locale)
Parameters :
This function generally accepts three parameters.
- N –
The number which is to be formatted. - D –
The number of decimal places to which the number is round off. - locale –
It’s an optional parameter, which decides a thousand separators and grouping between separators. By default, en_US locale is present in MySQL.
Returns :
The function formats the given number, round off it to a certain decimal place, and return the number in the form of string.
Example-1 :
FORMAT() function to round off given number to 2 decimal places.
SELECT FORMAT(555454.12365, 2) AS form;
Output :
form |
---|
555, 454.12 |
Example-2 :
FORMAT() function to round off given number with 0 decimal place.
SELECT FORMAT(130919999.456, 0) AS form;
Output :
form |
---|
130, 919, 999 |
Example-3 :
Replacing en_US locale with de_D locale.
SELECT FORMAT(27112020.1052, 3, 'de_DE') As form;
Output :
form |
---|
27.112.020, 105 |
Example-4 :
FORMAT() function to round off columns in table.
Creating a Product table –
CREATE TABLE Products( Product_Id INT AUTO_INCREMENT, Product_Name VARCHAR(100) NOT NULL, Price INT NOT NULL, PRIMARY KEY(Product_Id ) );
Inserting values into the table :
INSERT INTO Products(Product_Name, Price) VALUES ('MotorolaMobile', 75000.999 ), ('SmartWatch', 73000.455 ), ('Camera', 170000.545 ) ;
The table will look like as follows.
SELECT * FROM Products;
Product_Id | Product_Name | Price |
---|---|---|
1 | MotorolaMobile | 75000.999 |
2 | Smartwatch | 73000.455 |
3 | Camera | 170000.545 |
Now, formatting the Price column by rounding off up to 1 decimal places.
SELECT Product_Name, FORMAT(Price, 1) As New_price FROM Products;
Output :
Product_Name | New_price |
---|---|
MotorolaMobile | 75, 001.0 |
Smartwatch | 73, 000.5 |
Camera | 170, 000.5 |
Note –
The function FIND_IN_SET() works in MySQL version 5.6 and above.
Contact Us