Posts Tagged ‘TO_CHAR’

Tip: Getting rid of spaces after TO_CHAR

June 5th, 2008 No Comments   Posted in Oracle Tip

Have you ever notice the exatra space in you output when using the TO_CHAR function?  Want to get rid of it?  Here is how.

If I run this script:

select to_char(1234.89, '$999,990.00') num_format
from dual
/
select to_char(SYSDATE, 'Day, Month DD, YYYY') date_format
from dual
/

I get:

NUM_FORMAT
------------
   $1,234.89

1 row selected.

DATE_FORMAT
-----------------------------
Friday   , August    14, 2006

1 row selected.

Do you notice the lead spacing in the first statement and the odd spacing in the second?  That can be annoying. To get rid of it, just use the FM format mask modifier.

Now I can run this:

select to_char(1234.89, 'FM$999,990.00') num_format
from dual
/
select to_char(SYSDATE, 'FMDay, Month DD, YYYY') date_format
from dual
/

And I get:

NUM_FORMAT
------------
$1,234.89   

1 row selected.

DATE_FORMAT
-----------------------------
Friday, August 14, 2006      

1 row selected.


Tags: