Number format models|V4.3.3| docs|Distributed Database

Number format models

Last Updated：2024-12-02 03:48:26 Updated

Number format models specify the formats of fixed-point and floating-point numbers stored in a database. To convert a NUMBER, BINARY_FLOAT, or BINARY_ DOUBLE value in SQL statements into VARCHAR2 data, you can use number format models in functions.

Number format models in functions

Number format models can be used in the following functions:

To convert a value of the NUMBER, BINARY_FLOAT or BINARY_ DOUBLE data type in an expression, a condition, an SQL function, or an SQL statement into the VARCHAR2 data type, you can use a parameter of the TO_CHAR function to specify the format of the value. | Description | | |---------------|-----------------| | keywords | | | dir-name | | | dir-name-en | | | tenant-type | Oracle Mode |

Number format models specify the formats of fixed-point and floating-point numbers stored in a database. To convert a NUMBER, BINARY_FLOAT, or BINARY_ DOUBLE value in SQL statements into VARCHAR2 data, you can use number format models in functions.

Number format models in functions

Number format models can be used in the following functions:

To convert a value of the NUMBER, BINARY_FLOAT or BINARY_ DOUBLE data type in an expression, a condition, an SQL function, or an SQL statement into the VARCHAR2 data type, you can use a parameter of the TO_CHAR function to specify the format of the value.
To convert a value of the CHAR or VARCHAR2 data type in an expression, a condition, an SQL function, or an SQL statement to the NUMBER data type, you can use a parameter of the TO_NUMBER function (NLS_NUMERIC_CHARACTERS is not supported) to specify the format of the value.
To convert a value of the CHAR or VARCHAR2 data type in an expression, a condition, an SQL function, or an SQL statement to the BINARY_FLOAT or BINARY_DOUBLE data type, you can use a parameter of the TO_BINARY_FLOAT or TO_BINARY_DOUBLE function to specify the format of the value.

A number format model rounds a value to the specified number of significant digits. If a value has more significant digits on the left of the decimal place than specified in the format, the value is replaced with a number sign (#).

If a positive NUMBER value is extremely large and cannot be represented in the specified format, the value is replaced with an infinity sign (~). If a negative NUMBER value is extremely small and cannot be represented by the specified format, the value is replaced with a negative infinity sign (-~).

Elements of a number format model

Number format elements

A number format model consists of one or more number format elements. A negative return value automatically contains the leading minus sign (-), and a positive return value automatically contains the leading space, unless the format model contains a format element that represents positive or negative, such as MI, S, or PR.

The following table describes the elements contained in number format models and their usage.

Number format element	Example	Description
Comma (,)	9,999	Returns a comma in the specified position. You can specify multiple commas in a number format model. Restrictions: A number format model cannot start with a comma. A comma cannot appear to the right of a decimal point or period in a number format model.
Decimal point (.)	99.99	Returns a decimal point, which is a period (.) in the specified position. Restriction: You can specify only one decimal point in a number format model.
$	$9999	Returns a value with a leading dollar sign.
0	0999 9990	Returns the leading zeros. Leading zeros are usually represented as spaces. However, when the formatted value is zero, the leading zeros are 0s. Returns the trailing zeros.
9	9999	Returns a result with the specified number of digits. The result has a leading space if the formatted value is positive or a leading minus sign (-) if otherwise.
B	B9999	Returns spaces when the result is zero.
C	C999	Returns the ISO currency symbol (the current value of the `NLS_ISO_CURRENCY` parameter) in the specified position.
D	99D99	This element works the same as a decimal point (.) and can appear only once. The difference is that this element uses the default value of the `NLS_NUMERIC_CHARACTER` parameter.
EEEE	9.9EEEE	Returns a value in scientific notation.
G	9G999	This element works in the same way as a decimal point (.). The difference is that this element uses the default value of the `NLS_NUMERIC_CHARACTER` parameter.
L	L999	Returns the local currency symbol and uses the current value of the `NLS_CURRENCY` parameter in the specified position.
MI	9999MI	Returns a negative value with a trailing minus sign (-). Returns positive value with a trailing space. Restriction: The MI format element can appear only in the last position of a number format model.
PR	9999PR	Returns a negative value in a pair of angle brackets (<>). Returns a positive value with a leading space and a trailing space. Restriction: The PR format element can appear only in the last position of a number format model.
RN(rn)	RN rn	Returns a value as uppercase Roman numerals. Returns a value as lowercase Roman numerals. Restriction: The value can be an integer from 1 to 3999.
S	S9999 9999S	Returns a negative value with a leading minus sign (-). Returns a positive value with a leading plus sign (+). Returns a negative value with a trailing minus sign (-). Returns a positive value with a trailing plus sign (+). Restriction: The S format element can appear only in the first or last position of a number format model.
TM	TME TM9	The default value is `TM9`. A number with a fixed sign is returned unless the output exceeds 64 characters. If the output exceeds 64 characters, the number is returned automatically in scientific notation. When the output exceeds 64 bits, the output of `TM9` is equivalent to the output of `TME`.
U	U9999	Returns the Euro (or other) dual currency symbol represented by the current value of the `NLS_dual_currency` parameter in the specified position.
V(v)	999V99	Returns a value multiplied by 10ⁿ (rounded if necessary), where n is the quantity of 9s after V.
X(x)	XXXX xxxx	Returns a hexadecimal value with the specified number of digits. If the specified number is not an integer, it is rounded to an integer. Restrictions: Only positive values and 0 are allowed for the element. A negative value returns an error. You can precede this element only with 0 (which returns a leading zero) or FM. An error is returned for any other element. If you specify neither 0 nor FM together with X, the return value always has one leading space.
FM(fm)	FM999	Removes the leading space.

Conversion of formatted elements

The following table lists the values and the corresponding results converted based on the fmt argument by executing the SELECT TO_CHAR(number, 'fmt') FROM DUAL statement. If you omit the fmt argument, the value is converted into a VARCHAR2 value that is long enough to hold its significant digits.

Value	'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'

Examples

Convert the number 0 to a value with a decimal point, with 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 argument 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 TO_CHAR returns a value that is longer than 40 bytes or the input parameter is of the BINARY_DOUBLE or BINARY_FLOAT data type, the result is converted into scientific notation.

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

To convert a value of the CHAR or VARCHAR2 data type in an expression, a condition, an SQL function, or an SQL statement to the NUMBER data type, you can use a parameter of the TO_NUMBER function (NLS_NUMERIC_CHARACTERS is not supported) to specify the format of the value.
To convert a value of the CHAR or VARCHAR2 data type in an expression, a condition, an SQL function, or an SQL statement to the BINARY_FLOAT or BINARY_DOUBLE data type, you can use a parameter of the TO_BINARY_FLOAT or TO_BINARY_DOUBLE function to specify the format of the value.