Number format models |V3.2.4|OceanBase Database| docs|Distributed Database

Number format models

Last Updated：2023-10-24 09:23:04 Updated

Number format models specify the formats of fixed-point and floating-point numbers stored in a database. If you need 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 elements

A number format model consists of one or more number format elements. The returned value has a leading minus sign (-) if negative or a leading space if positive, unless the format model contains a format element that indicates whether a number is positive or negative, for example, MI, S, or PR.

The following table describes the supported number format elements.

Number format element	Example	Description
, (comma)	9,999	Returns a comma (,) at the specified position. You can specify multiple commas (,) in the number format model. Limits: Commas (,) cannot appear at the beginning of the number format model. Commas (,) cannot appear on the right of decimal characters or periods (.) in the number format model.
. (decimal point)	99.99	Returns a decimal point at the specified position. Limits: You can specify only one decimal point in the number format model.
$	$9999	Returns a value with a leading dollar sign ($).
0	0999 9990	Returns a leading zero. Returns a trailing zero.
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 a space 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 has the same meaning as the 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 the scientific notation format.
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 a positive value with a trailing space. Limits: The MI element can appear only at the end of the number format model.
PR	9999PR	Returns a negative value in angle brackets (< >). Returns a positive value with leading and trailing spaces. Limits: The PR element can appear only at the end of the number format model.
RN(rn)	RN rn	Returns a value in the uppercase Roman numeral format. Returns a value in the lowercase Roman numeral format. Limits: The value must be an integer ranging 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 (+). Limits: The S element can appear only at the beginning or end of the number format model.
TM	TME TM9	Returns a number that contains 64 or fewer digits as a fixed symbol. Default value: `TM9`. Returns a number that contains more than 64 digits in the scientific notation format. If a number contains more than 64 digits, the output of `TM9` is equivalent to that of `TME`.
U	U9999	Returns double euro signs or other currency symbols at the specified position. This element uses the current value of the `NLS_dual_currency` parameter.
V(v)	999V99	Returns a value multiplied by 10n (rounded if necessary). 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. Limits: The value of this element must be positive or 0. The system returns an error if the value is negative. You can add only 0 (in this case, a leading zero is returned) or FM to the beginning of this element. The system returns an error if you add any other element to the beginning of this element. If you use this element without specifying 0 or FM, the returned value always has a leading space.
FM(fm)	FM999	Removes leading spaces.

You can use number format models in the following functions:

TO_CHAR function: Convert a NUMBER, BINARY_FLOAT, or BINARY_DOUBLE value into VARCHAR2 data.
TO_NUMBER function: Convert 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 element can appear only at the beginning or end of the 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 (0.00 sec)

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