Oracle Tips and Techniques

Numbers are usually converted into characters implicitly by Oracle. However
If you can to convert number into some specific format you can use Oracle’s
TO_CHAR function.

Note: While converting to a format the format mask must cover the entire length of
the number. If the number is larger than the mask then you will get ######## returned
which means the TO_CHAR function could not convert the number given to it.

The format to TO_CHAR number function is

to_char( value, [ format_mask ], [ nls_language ] )

Some examples are given below:

select to_char(100, ’9999.99′) from dual
– converts to 100.00

select to_char(100.1, ’9999.99′) from dual
– converts to 100.10

select to_char(100.73, ‘C9999.99′) from dual
–converts to GBP100.73

select to_char(1000.73, ‘L9999.99′) from dual
–converts to #1000.73

select to_char(1000.73, ‘C9999.00′) from dual
–converts to GBP1000.73

select to_char(1000.73, ‘C99,999,999.99′) from dual
– converts to      GBP1,000.73

select to_char(10000000.73, ‘C99,999,999.00′) from dual
– converts to GBP10,000,000.73

select to_char(10000000.73, ‘C99,999,999.99S’) from dual
–converts to GBP10,000,000.73+

select to_char(1000.73, ‘C99,999,999.99′) from dual
– converts to ‘      GBP1,000.73′
Note the spaces. This is returned by Oracle, not typo here.

select to_char(1111.73, ‘FM99,999,999.99′) from dual
–converts to 1,111.73
–notice that spaces are gone. FM suppressed the leading spaces.

select to_char(100000, ’9999.99′) from dual
– convert to ########
– As the number is larger than the given format the function could not convert it.