FORMAT() function in MySQL
Last Updated :
07 Dec, 2020
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 :
Example-2 :
FORMAT() function to round off given number with 0 decimal place.
SELECT FORMAT(130919999.456, 0)
AS form;
Output :
Example-3 :
Replacing en_US locale with de_D locale.
SELECT FORMAT(27112020.1052, 3, 'de_DE') As form;
Output :
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.
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...