Oracle Number Format

Here you will find list of Oracle Number Format elements:

Format element	Description
,	Returns a comma in the specified position it is possible to specify multiple commas.
.	Returns a period in the specified position.
$	Returns a leading dollar sign.
0	Returns leading or trailing zeros (0).
9	Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative.
B	Returns blanks for the integer part of a fixed point number when the integer part is zero.
C	Returns in the specified position the ISO currency symbol. Default currency value is set in the NLS_ISO_CURRENCY parameter.
D	Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period.
EEEE	Returns a value in scientific notation.
FM	Returns a value without blanks (leading and trailing).
G	Returns in the specified position the group separator. The defoult value is set in the NLS_NUMERIC_CHARACTER parameter. You can specify multiple group separators in a number format model.
L	Returns in the specified position the local currency symbol. Default currency value is set in the NLS_CURRENCY parameter. Format element for Oracle currency formatting.
MI	Returns negative value with a trailing minus (-)  sign. Returns positive value with a trailing blank.
PR	Returns negative value in <>. Returns positive value with a leading and trailing blank.
RN	Returns a value as Roman numerals in uppercase. Allowed value is an integer between 1 and 3999.
rn	Returns a value as Roman numerals in lowercase. Allowed value is an integer between 1 and 3999.
S	Returns value with a leading or trailing minus (-) or plus (+) sign.
TM	The text minimum number format model returns the smallest number of characters possible. This element is case insensitive.
U	Returns in the specified position the defoult currency symbol. The current value of the NLS_DUAL_CURRENCY parameter.
V	Returns a value multiplied by 10n and if necessary, round it up.
X	Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, Oracle Database will round it to an integer.

In examples below are shown conversions from number to char with different numbers and format elements.

Examples:

--Template:

SELECT TO_CHAR(number, 'format_element')
  FROM dual;

SELECT TO_CHAR(-55555, '99999S')
  FROM dual;

--Result: 55555-

SELECT TO_CHAR(0, '99.99')
  FROM dual;

--Result: .00

SELECT TO_CHAR(+0.5, '99.99')
  FROM dual;
--Result: .50

SELECT TO_CHAR(-0.5, '99.99')
  FROM dual;

--Result: -.50

SELECT TO_CHAR(+0.55, '90.99')
  FROM dual;

--Result: 0.55

SELECT TO_CHAR(-0.5, '90.99')
  FROM dual;

--Result: -0.50

SELECT TO_CHAR(0, '9999')
  FROM dual;

--Result: 0

SELECT TO_CHAR(+222.55, '999.99')
  FROM dual;

--Result: 222.55

SELECT TO_CHAR(-222.55, '999.99')
  FROM dual;

--Result: -222.55

SELECT TO_CHAR(+222.55, 'FM999.09')
  FROM dual;

--Result: 222.55

SELECT TO_CHAR(+555.0, 'FM999.009')
  FROM dual;

--Result: 555.00

Oracle currency formatting examples:

SELECT TO_CHAR(+55.55, 'L99.99')
  FROM dual;

--Result: $55.55

SELECT TO_CHAR(+55.5, 'L99.9')
  FROM dual;

--Result: $55.5

SELECT  TO_CHAR(100000000000, 'L99G999G999G9999', 'NLS_NUMERIC_CHARACTERS=",."')
FROM dual

--Result: $10,000,000,0000

SELECT  TO_CHAR(100000000000, 'L99G999G999G9999')
FROM dual  

--Result: $10.000.000.0000

 

Examples in table: