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

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.

Elements of a number format model

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 supported number format elements.

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 character or period in a number format model.
Decimal point (.)	99.99	Returns a decimal point at 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. Returns the trailing zeros.
9	9999	Returns a value with the specified number of digits. The value has a leading space if positive or a leading minus sign (-) if negative. Leading zeros are blank, except for a zero value, which returns a zero for the fractional part of the fixed-point number.
B	B9999	Returns spaces when the result is zero.
C	C999	Returns an ISO currency symbol (value of the `NLS_ISO_CURRENCY` parameter) at 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 has the same meaning as the 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 at the specified position. This element uses the current value of the `NLS_CURRENCY` parameter.
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 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^n (rounded up if necessary), where n is the number 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. The system returns an error if the value is negative. 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.

Number format models can be used in the following functions:

TO_CHAR function: converts a NUMBER, BINARY_FLOAT, or BINARY_DOUBLE value into VARCHAR2 data.
TO_NUMBER function: converts a CHAR or VARCHAR2 value into NUMBER data.
TO_BINARY_FLOAT and TO_BINARY_DOUBLE functions: convert CHAR and VARCHAR2 expressions into BINARY_FLOAT or BINARY_DOUBLE data.

Examples

The S format element can appear only in the first or last position of a number format model. When the element appears at the beginning, the value has a leading plus sign (+) if positive or a leading minus sign (-) if negative. When the element appears at the end, the value has a trailing plus sign (+) if positive or a trailing minus sign (-) if negative.

obclient> SELECT TO_CHAR(-1234567890, '9999999999S') FROM DUAL;
+------------------------------------+
| TO_CHAR(-1234567890,'9999999999S') |
+------------------------------------+
| 1234567890-                        |
+------------------------------------+
1 row in set

obclient> SELECT TO_CHAR(1234567890, '9999999999S') FROM DUAL;
+-----------------------------------+
| TO_CHAR(1234567890,'9999999999S') |
+-----------------------------------+
| 1234567890+                       |
+-----------------------------------+
1 row in set

The 9 element returns a value with the specified number of digits. The value has a leading space if positive or a leading minus sign (-) if negative. Leading zeros are blank, except for a zero value, which returns a zero for the fractional part of a fixed-point number. In the following example, two zeros (00) are added to the fractional part.

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

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

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

The B element returns a space when the result is zero.

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

The FM element specifies to remove leading spaces.

obclient> SELECT TO_CHAR(123.456, 'FM999.009') FROM DUAL;
+------------------------------+
| TO_CHAR(123.456,'FM999.009') |
+------------------------------+
| 123.456                      |
+------------------------------+
1 row in set

The EEEE element returns a value in the scientific notation format.

obclient> SELECT TO_CHAR(123.456, '9.9EEEE') FROM DUAL;
+----------------------------+
| TO_CHAR(123.456,'9.9EEEE') |
+----------------------------+
|   1.2E+02                  |
+----------------------------+
1 row in set

obclient> SELECT TO_CHAR(123.456, 'FM9.9EEEE') FROM DUAL;
+------------------------------+
| TO_CHAR(123.456,'FM9.9EEEE') |
+------------------------------+
| 1.2E+02                      |
+------------------------------+
1 row in set

The L element returns the local currency symbol at the specified position. This element uses the current value of the NLS_CURRENCY parameter.

obclient> SELECT TO_CHAR(123.456, 'FML999.99') FROM DUAL;
+------------------------------+
| TO_CHAR(123.456,'FML999.99') |
+------------------------------+
| $123.46                      |
+------------------------------+
1 row in set

OceanBase

Customer Stories

Documentation

Number format models

Elements of a number format model

Examples