How the FORMAT() function works in Mariadb?

The FORMAT() function is a string function that formats and returns the given number as a string, adding separators to appropriate positions and rounding the results to the given decimal position.

Posted on

The FORMAT() function is a string function that formats and returns the given number as a string, adding separators to appropriate positions and rounding the results to the given decimal position. It can be used to display numbers in a readable and consistent way.

Syntax

The syntax of the FORMAT() function is as follows:

FORMAT(num, decimal_position [, locale])

The function takes two or three arguments:

  • num is the number to be formatted.
  • decimal_position is the number of decimal places to round the result to.
  • locale is an optional argument that specifies the locale to use for formatting the number. If not specified, the default locale is used.

The function returns a string value that represents the formatted number. If any of the arguments is NULL, the function returns NULL.

Examples

Example 1: Using FORMAT() function with positive numbers

The following example shows how to use the FORMAT() function with positive numbers:

SELECT FORMAT(1234567890.09876543210, 4), FORMAT(1234567.89, 4), FORMAT(1234567.89, 0) ;

The function returns the formatted numbers with separators and decimal places.

+-----------------------------------+-----------------------+-----------------------+
| FORMAT(1234567890.09876543210, 4) | FORMAT(1234567.89, 4) | FORMAT(1234567.89, 0) |
+-----------------------------------+-----------------------+-----------------------+
| 1,234,567,890.0988                | 1,234,567.8900        | 1,234,568             |
+-----------------------------------+-----------------------+-----------------------+

Example 2: Using FORMAT() function with negative numbers

The following example shows how to use the FORMAT() function with negative numbers:

SELECT FORMAT(-1234567890.09876543210, 4), FORMAT(-1234567.89, 4), FORMAT(-1234567.89, 0);

The function returns the formatted numbers with separators and decimal places, and preserves the negative sign.

+------------------------------------+------------------------+------------------------+
| FORMAT(-1234567890.09876543210, 4) | FORMAT(-1234567.89, 4) | FORMAT(-1234567.89, 0) |
+------------------------------------+------------------------+------------------------+
| -1,234,567,890.0988                | -1,234,567.8900        | -1,234,568             |
+------------------------------------+------------------------+------------------------+

Example 3: Using FORMAT() function with zero

The following example shows how to use the FORMAT() function with zero:

SELECT FORMAT(0, 4), FORMAT(0, 0);

The function returns zero with the specified decimal places.

+--------------+--------------+
| FORMAT(0, 4) | FORMAT(0, 0) |
+--------------+--------------+
| 0.0000       | 0            |
+--------------+--------------+

Example 4: Using FORMAT() function with NULL

The following example shows how to use the FORMAT() function with NULL:

SELECT FORMAT(NULL, 4), FORMAT(1234.56, NULL), FORMAT(NULL, NULL);

The function returns NULL, because NULL is not a valid number or decimal position.

+-----------------+-----------------------+--------------------+
| FORMAT(NULL, 4) | FORMAT(1234.56, NULL) | FORMAT(NULL, NULL) |
+-----------------+-----------------------+--------------------+
| NULL            | NULL                  | NULL               |
+-----------------+-----------------------+--------------------+

Some of the functions that are related to the FORMAT() function are:

  • ROUND():This function returns a number rounded to a specified number of decimal places. It can be used to round a number to a desired precision, or to remove the decimal part of a number. For example, ROUND(1.567, 2) returns 1.57, and ROUND(1.567, 0) returns 2.
  • TRUNCATE():This function returns a number truncated to a specified number of decimal places. It can be used to remove the fractional part of a number without rounding. For example, TRUNCATE(1.567, 2) returns 1.56, and TRUNCATE(1.567, 0) returns 1.
  • CONVERT():This function converts a value from one data type to another. It can be used to convert a number to a string, or vice versa. For example, CONVERT(1234.56, CHAR) returns ‘1234.56’, and CONVERT('1234.56', DECIMAL(10,2)) returns 1234.56.