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) AS 'Format';
SELECT FORMAT(1234567.89, 4) AS 'Format';
SELECT FORMAT(1234567.89, 0) AS 'Format';

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

+--------------------+
| Format             |
+--------------------+
| 1,234,567,890.0988 |
+--------------------+

+----------------+
| Format         |
+----------------+
| 1,234,567.8900 |
+----------------+

+-----------+
| Format    |
+-----------+
| 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) AS 'Format';
SELECT FORMAT(-1234567.89, 4) AS 'Format';
SELECT FORMAT(-1234567.89, 0) AS 'Format';

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

+---------------------+
| Format              |
+---------------------+
| -1,234,567,890.0988 |
+---------------------+

+-----------------+
| Format          |
+-----------------+
| -1,234,567.8900 |
+-----------------+

+------------+
| Format     |
+------------+
| -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) AS 'Format';
SELECT FORMAT(0, 0) AS 'Format';

The function returns zero with the specified decimal places.

+---------+
| Format  |
+---------+
| 0.0000  |
+---------+

+--------+
| Format |
+--------+
| 0      |
+--------+

Example 4: Using FORMAT() function with NULL

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

SELECT FORMAT(NULL, 4) AS 'Format';
SELECT FORMAT(1234.56, NULL) AS 'Format';
SELECT FORMAT(NULL, NULL) AS 'Format';

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

+--------+
| Format |
+--------+
| NULL   |
+--------+

+--------+
| Format |
+--------+
| NULL   |
+--------+

+--------+
| Format |
+--------+
| NULL   |
+--------+

Example 5: Using FORMAT() function with locale

The following example shows how to use the FORMAT() function with locale. Suppose we have a table called sales that has the following data:

+----+---------+-------+--------+
| id | product | price | amount |
+----+---------+-------+--------+
|  1 | A       | 10.50 |     10 |
|  2 | B       | 20.00 |     15 |
|  3 | C       | 15.75 |     12 |
|  4 | D       | 25.00 |     20 |
|  5 | E       | 30.00 |     25 |
+----+---------+-------+--------+

We can use the FORMAT() function to display the price and amount of each product in different locales, such as ’en_US’, ‘de_DE’, and ‘zh_CN’:

SELECT product, FORMAT(price, 2, 'en_US') AS 'Price (en_US)', FORMAT(price, 2, 'de_DE') AS 'Price (de_DE)', FORMAT(price, 2, 'zh_CN') AS 'Price (zh_CN)'
FROM sales;

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

+---------+---------------+---------------+---------------+
| product | Price (en_US) | Price (de_DE) | Price (zh_CN) |
+---------+---------------+---------------+---------------+
| A       | 10.50         | 10,50         | 10.50         |
| B       | 20.00         | 20,00         | 20.00         |
| C       | 15.75         | 15,75         | 15.75         |
| D       | 25.00         | 25,00         | 25.00         |
| E       | 30.00         | 30,00         | 30.00         |
+---------+---------------+---------------+---------------+

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

  • ROUND() function: 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() function: 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() function: 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.