Number format models

Number format models specify the format of fixed-point and floating-point numbers stored in the database. When you need to convert the values of the NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data types in an SQL statement to the VARCHAR2 data type, you can use the number format models in the function.

Number format models in functions

You can use number format models in the following functions:

• When NUMBER, BINARY_FLOAT, or BINARY_DOUBLE appears in an expression, condition, SQL function, or SQL statement, you can use the parameters of the TO_CHAR function to specify the format of these values when converting them to the VARCHAR2 data type.

• When CHAR or VARCHAR2 appears in an expression, condition, SQL function, or SQL statement, you can use the parameters of the TO_NUMBER function (excluding NLS_NUMERIC_CHARACTERS) to specify the format of these values when converting them to the NUMBER data type.

• When CHAR or VARCHAR2 appears in an expression, condition, SQL function, or SQL statement, you can use the parameters of the TO_BINARY_FLOAT and TO_BINARY_DOUBLE functions to specify the format of these values when converting them to the BINARY_FLOAT or BINARY_DOUBLE data types.

Number format models round the values and retain the specified number of significant digits. If the number of decimal places in a value exceeds the number of decimal places specified in the format, the value is replaced with a #.

If a positive NUMBER value is too large to be represented in the specified format, it is replaced with the infinity symbol (~). If a negative NUMBER value is too small to be represented in the specified format, it is replaced with the negative infinity symbol (-~).

Elements of a numeric format model

Number format elements

A number format model consists of one or more number format elements. Negative return values automatically include a leading minus sign, and positive values automatically include a leading space, unless the format model includes elements such as MI, S, or PR that indicate the sign of a number.

The following table lists the elements included in a number format model and their specific usage.

Example of element formatting

The following table shows the conversion results when you execute the SQL command SELECT TO_CHAR(number, 'fmt') FROM DUAL; for different numbers with the format element parameter fmt. If you omit the format model parameter fmt, the number is converted to a VARCHAR2 of sufficient length to retain all its significant digits.

Examples

• Convert the value 0 to a value with a decimal point and two significant digits after the decimal point.

  obclient> SELECT TO_CHAR(0, '99.99') FROM DUAL;
  +--------------------+
  | TO_CHAR(0,'99.99') |
  +--------------------+
  |    .00             |
  +--------------------+
  1 row in set
  

• Omit the fmt parameter and retain all significant digits of the value.

  obclient> SELECT TO_CHAR(123.456) FROM DUAL;
  +------------------+
  | TO_CHAR(123.456) |
  +------------------+
  | 123.456          |
  +------------------+
  1 row in set
  

• If the result of TO_CHAR exceeds 40 bytes or the input parameter is of type BINARY_DOUBLE or BINARY_FLOAT, the result is converted to scientific notation.

  obclient> SELECT TO_CHAR(12355555555555555555555555555555555555555555555555) FROM DUAL;
  +-------------------------------------------------------------+
  | TO_CHAR(12355555555555555555555555555555555555555555555555) |
  +-------------------------------------------------------------+
  | 1.2355555555555555555555555555555556E+49                    |
  +-------------------------------------------------------------+
  1 row in set