MySQL8 FORMAT() Functions – String Functions

by · Published · Updated

MySQL FORMAT() Functions: Syntax

FORMAT( number, decimal [,locale]);

MySQL FORMAT() Functions: Description

As the name specified format(), it used to format the numbers. So, that the number can be read easily. This can be done as per the locale specified or by default it is ‘en_US’.

Format() function, Formats the number to a specified format like ‘#,###,###.##’, rounded to specified decimal places, and returns the result as a string.

If decimal value is 0, the result has no decimal point or fractional part. Decimal part gets removed.

The third parameter is Optional. It is used to enable locale to format a number. It is used for the result number’s decimal point, thousands separator, and grouping between separators. Permissible locale values are the same as the legal values for the lc_time_names system variable.

Note : If no locale is specified, the default is ‘en_US’.

MySQL FORMAT() Functions: Parameter

[table caption=”” width=”100%” colwidth=”15%|30%|55%” colalign=”left|left|left”]
Name, Required /Optional, Description
number, Required, The input number to be formatted.
decimal, Required, It specifies upto which places a number should have decimal values. If its value is 0 then string will be returned without values after decimal values.
locale , Optional, It is optional. Default value is ‘en_US’.

[/table]

MySQL FORMAT() Functions: Output

[table caption=”” width=”100%” colwidth=”20%|80%” colalign=”left|left”]
Returns,
string , After applying the formatting.
[/table]

MySQL FORMAT() Functions: Available from

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Version, MySQL 4.0
[/table]

Related articles :  CONCAT_WS() ,LOWER(), UPPER(), TRIM().

MySQL FORMAT() Functions: Example 1

Formatting the number fraction part upto 4 decimal places. It will truncate the decimal values upto 4 digits.

mysql>  SELECT FORMAT(12345.123456, 4);
+-----------------------------+
| FORMAT(12345.123456, 4) |
+-----------------------------+
| 12,345.1235                          |
+-----------------------------+

MySQL FORMAT() Functions: Example 2

Formatting the number fraction part upto 4 decimal places. It will add 0 in the 2 decimal values.

mysql> SELECT FORMAT(12345.12, 4);
+---------------------+
| FORMAT(12345.12, 4) |
+---------------------+
| 12,345.1200                               |
+---------------------+

MySQL FORMAT() Functions: Example 3

Formatting the number fraction part upto 0 decimal places. This will not return the fractional part, as decimal value paramerter value is 0.

mysql> SELECT FORMAT(12345.5, 0);
+--------------------+
| FORMAT(12345.5, 0) |
+--------------------+
| 12,346                              |
+--------------------+

MySQL FORMAT() Functions: Example 4

Formatting the number fraction part upto 2 decimal places and locale value is ‘de_DE’.

mysql> SELECT FORMAT(12345.2,2,'de_DE');
+------------------------------------+
| FORMAT(12345.2,2,'de_DE') |
+------------------------------------+
| 12.345,20                              |
+------------------------------------+

See all MySQL String functions MySQL 8 String Functions.

Related articles : LOCATE(), HEX(), CONCAT(), LOWER(), LTRIM(), INSTR(), POSITION(), FIELD().

PHP Related articles : MONEY_FORMAT(), NUMBER_FORMAT(), PRINTF(), PHP STRING FUNCTIONS(), FPRINTF().

You may also like...