MySQL FORMAT() Function

In MySQL, the FORMAT() function round a specified number to the specified decimal places, and format like '#,###,###.##', and return the result as a string.

FORMAT() Syntax

Here is the syntax of MySQL FORMAT() function:

FORMAT(number, decimal_places)

Parameters

number

Required. The Number that needs to be formatted.

decimal_places

Required. The decimal places to retain.

Return value

The FORMAT() function returns a string like #,###,###.## format.

If the number parameter is NULL, it will return NULL.

FORMAT() Examples

SELECT
    FORMAT(111111, 2),
    FORMAT(111111.111, 2),
    FORMAT(111111.111, 0),
    FORMAT('111111', 2),
    FORMAT('11111A', 2),
    FORMAT('A11111', 2),
    FORMAT(NULL, 2)\G

    FORMAT(111111, 2): 111,111.00
FORMAT(111111.111, 2): 111,111.11
FORMAT(111111.111, 0): 111,111
  FORMAT('111111', 2): 111,111.00
  FORMAT('11111A', 2): 11,111.00
  FORMAT('A11111', 2): 0.00
      FORMAT(NULL, 2): NULL
1 row in set, 2 warnings (0.00 sec)