Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

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 :

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_IdProduct_NamePrice
1MotorolaMobile75000.999
2Smartwatch73000.455
3Camera170000.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_NameNew_price
MotorolaMobile75, 001.0
Smartwatch73, 000.5
Camera170, 000.5

Note –
The function FIND_IN_SET() works in MySQL version 5.6 and above.

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!