All Products
Search
Document Center

Number formatting

Last Updated: Jun 18, 2021

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.

Element

Example

Description

. (decimal point)

99.99

Returns a decimal number where the decimal point is in the specified place.

Constraint: In number formatting, you can specify only one decimal point.

0

0999 9990

For 0999, leading zero values are returned. For 9990, trailing zero values are returned.

9

9999

Returns a value that has the specified number of digits. If the value is positive, a number with leading white-space characters is returned. If the value is negative, a number with a leading minus sign (-) is returned. Leading zeros are not displayed and are replaced with white-space characters for all of the numeric values except zero values. A zero is returned for each non-leading zero value that is included in the integer part of the fixed-point number.

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;

number

'fmt'

Result

0

99.99

' .00'

+0.1

99.99

' .10'

-0.2

99.99

' -.20'

0

90.99

' 0.00'

+0.1

90.99

' 0.10'

-0.2

90.99

' -0.20'

0

9999

' 0'

1

9999

' 1'

+123.456

999.999

' 123.456'

-123.456

999.999

'-123.456'

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                    |
+-------------------------------------------------------------+