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.