numerical format elements that can be used in format masks

Format Element Description Example

$ append dollar sign at the beginning. TO_CHAR(1234,'$9999')

0 Returns leading and/or trailing zeroes. TO_CHAR(1234,'09999')

9 specified number of digits, TO_CHAR(1234,'9999')
adding leading blank space for positive numbers or
leading minus sign for negatives.

B Returns blanks for the integer of a fixed point number TO_CHAR(1234,'B9999')

C Returns ISO currency symbol. TO_CHAR(1234,'C9999')

D Returns ISO decimal character. TO_CHAR(1234.5,'99D99')

EEEE Returns value in scientific notation. TO_CHAR(1234,'9.9EEEE')

FM Returns value with no leading or trailing blank spaces. TO_CHAR(1234,'FM9999')

MI Returns negative value with the trailing minus sign; TO_CHAR(-1234,'9999MI')
positive values are returned with a trailing blank space.

PR Returns a negative value in the angle brackets, and TO_CHAR(-1234,'9999PR')
returns a positive value with leading and trailing blank spaces.

RN / rn Returns value as a Roman numeral in uppercase/or lowercase. TO_CHAR(1234,'RN')

S Appends minus or plus signs. TO_CHAR(1234,'S9999')

X Returns hexadecimal value of the specified number of digits; TO_CHAR(1234,'XXXX')
noninteger values get rounded.

1.	Use To_Char function to format number in a select query
2.	TO_CHAR(): return '#' if format is short
3.	Use to_char( sal, '$9,999.99' ) to format a number type column to currency format
4.	Without enough number symbols, Oracle is unable to return any output
5.	to_char( 111, '099.99' )

numerical format elements that can be used in format masks : TO_CHAR Number « Data Type « Oracle PL / SQL