Number formatting

Number formatting specifies the formats of fixed-point and floating-point numbers that are stored in databases.

Number formatting in functions

The following functions for numeric type conversion use number formatting:

• Assume that NUMBER, BINARY_FLOAT, or BINARY_ DOUBLE values appear in expressions, conditions, SQL functions, and SQL statements. If you need to convert these values to VARCHAR2 values, set the corresponding parameters of the TO_CHAR function to specify the formats of these numeric values.

• Assume that CHAR or VARCHAR2 values appear in expressions, conditions, SQL functions, and SQL statements. If you need to convert these values to NUMBER values, set the corresponding parameters of the TO_NUMBER function to specify the formats of these numeric values. The NLS_NUMERIC_CHARACTERS parameter is not supported. If you need to convert the values to BINARY_FLOAT or BINARY_DOUBLE values, set the corresponding parameters of TO_BINARY_FLOAT and TO_BINARY_DOUBLE functions to specify the formats of these numeric values.

Number formatting rounds a value to the specified number of significant digits. If a value has a decimal part and the number of significant digits to the left is greater than that specified in the format, the value is replaced with #. If the positive value of the NUMBER type is extremely large and cannot be represented in the specified format, the value is replaced with the infinity symbol (~). If the negative value of the NUMBER type is extremely small and cannot be represented in the specified format, the value is replaced with the negative infinity symbol (-~).

Number format elements

ApsaraDB for OceanBase is different from Oracle databases because the number format elements in ApsaraDB for OceanBase support only standard numeric formats. The following table lists the number format elements that are supported by ApsaraDB for OceanBase.

If the format parameter is omitted, ApsaraDB for OceanBase converts the numeric value to a VARCHAR2 value of a sufficient length to retain all of the significant digits of the value.

Examples

Execute the following statement:

SELECT TO_CHAR(0, '99.99') FROM DUAL;

The following query result is returned:

+--------------------+
| TO_CHAR(0,'99.99') |
+--------------------+
|    .00             |
+--------------------+

The following table lists the results of queries where number is set to different values and the 'fmt' format element is used.

SELECT TO_CHAR(number, 'fmt')  FROM DUAL;

When the 'fmt' parameter is omitted, the following statement is executed:

SELECT TO_CHAR(123.456) FROM DUAL;

The following result is displayed:

+------------------+
| TO_CHAR(123.456) |
+------------------+
| 123.456          |
+------------------+

When the TO_CHAR result is more than 40 bytes in length or the input parameter is of the BINARY_DOUBLE or BINARY_FLOAT type, the result is in the scientific notation format.

Example:

SELECT TO_CHAR(12355555555555555555555555555555555555555555555555) FROM DUAL;

The following result is displayed:

+-------------------------------------------------------------+
| TO_CHAR(12355555555555555555555555555555555555555555555555) |
+-------------------------------------------------------------+
| 1.2355555555555555555555555555555556E+49                    |
+-------------------------------------------------------------+