List of Number Format Elements in Oracle

Below is a full list of the format elements that can be used to format numbers in Oracle Database.

Element Example Description

, 9,999 Returns a comma in the specified position. You can specify multiple commas. A number format model cannot begin with a comma. Also, a comma cannot appear to the right of a decimal character or period.

. 99.99 Returns a decimal point, which is a period (.) in the specified position. Only one period is allowed in a number format model.

$ $9999 Returns value with a leading dollar sign.

0 099990 Returns leading zeros and/or trailing zeros.

9 9999 Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.

B B9999 Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model).

C C999 ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter).

D 99D99 Returns the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period (.). Only one decimal character is allowed in a number format model.

EEEE 9.9EEEE Returns the value using scientific notation.

G 9G999 Group separator (the current value of the NLS_NUMERIC_CHARACTER parameter). You can specify multiple group separators in a number format model, but a group separator cannot appear to the right of a decimal character or period.

L L999 Local currency symbol (the current value of the NLS_CURRENCY parameter).

MI 9999MI Returns negative values with a trailing minus sign (-). Returns positive value with a trailing blank. Only allowed in the last position of a number format model.

PR 9999PR Returns negative value in angle brackets (<>). Returns positive value with a leading and trailing blank. Only allowed in the last position of a number format model.

RN RN Returns the value as Roman numerals in uppercase. Value can be an integer between 1 and 3999.

rn rn Returns the value as Roman numerals in lowercase. Value can be an integer between 1 and 3999.

S S9999

9999S Returns negative values with a leading or trailing minus sign (-). Returns positive values with a leading or trailing plus sign (+). Only allowed in the first or last position.

TM TM The text minimum number format model returns (in decimal output) the smallest number of characters possible. Case insensitive. The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If the output exceeds 64 characters, then the number is returned in scientific notation.

You cannot precede this element with any other element. You can follow this element only with one 9 or one E (or e), but not with any combination of these.

U U9999 Returns in the specified position the Euro (or other) dual currency symbol, determined by the current value of the NLS_DUAL_CURRENCY parameter.

V 999V99 Returns a value multiplied by 10n (and if necessary, rounds it up), where n is the number of 9s after the V.

X

x XXXX

xxxx Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle Database rounds it to an integer.

Only accepts positive values or 0. Negative values return an error. You can precede this element only with 0 (which returns leading zeroes) or FM. Any other elements return an error. If you specify neither 0 nor FM with X, then the return always has one leading blank.

How to Use Format Elements

Format elements (sometimes referred to as format specifiers) can be used to construct format models (sometimes referred to as format strings), which determine how the number is displayed.

For example:

SELECT TO_CHAR(2735.56, 'L99G999D99MI') AS Result

FROM DUAL;

Result:

          $2,735.56 

The fm format modifier can also be used to remove any padding that may have been automatically applied to the result.

Example:

SELECT TO_CHAR(2735.56, 'fmL99G999D99MI') AS Result

FROM DUAL;

Result:

$2,735.56

Fuentes.

Artículo:   "List of Number Format Elements in Oracle" Publicado en https://database.guide por Ian el  August 31, 2021. Consultado el 30/06/2024.

URL: https://database.guide/list-of-number-format-elements-in-oracle/