Remove leading space in to_char

One point which is usually overseen with the usage of TO_CHAR() is the leading space that it returns when converting a number to character.

Example:
select length(to_char('109','000.00')) from dual;

TO_CHAR('109','000.00')
" 109.00"

Do you notice the leading space that TO_CHAR() has returned? If you have not noticed and surprised, execute this and find it for yourself.

This is not a bug.

The Reason is Oracle reserves the first character for Sign. If you won't beleive me, execute the following statement:

select to_char('-109','000.00') from dual;

TO_CHAR('-109','000.00')

"-109.00"

Notice that the length of the string has not increased with the negative value being converted by TO_CHAR().

Hold on your tendency of calling TRIM() function to trim out the space. TO_CHAR() has a format model that suppresses this trailing space.

To suppress this leading space simply use the FM (stands for Fill Mode) in your format string.

select to_char('109','fm000.00') from dual;

TO_CHAR('109','fm000.00')

"109.00"

In case you don't need any specific format for the string, but just to ignore the trailing space, use the TM (stands for Text Minimum) in your format string.

select to_char('109.90','tm') from dual;

TO_CHAR('109','tm')

"109"

Further Reading: Ask Tom, Oracle Documentation for Format Models