Why is Oracle's to_char() function adding spaces?
select length('012'),
length(to_char('012')),
length(to_char('12', '000'))
from dual;
3, 3, 4
-
Similar question stackoverflow.com/questions/156329/… has more detailed answers.Vadzim– Vadzim2015-01-14 13:15:37 +00:00Commented Jan 14, 2015 at 13:15
Add a comment
|
4 Answers
The extra leading space is for the potential minus sign. To remove the space you can use FM in the format:
SQL> select to_char(12,'FM000') from dual;
TO_C
----
012
By the way, note that to_char takes a NUMBER argument; to_char('012') is implicitly converted to to_char(to_number('012')) = to_char(12)
1 Comment
Andreas Covidiot
just for clarification:
to_char(-12,'FM00') would result in -12 and not (as one may fear) 12 or ##To make the answers given more clear:
select '['||to_char(12, '000')||']',
'['||to_char(-12, '000')||']',
'['||to_char(12,'FM000')||']'
from dual
[ 012] [-012] [012]
Comments
The format mask that you are using is fixed width and allows for a minus sign
Comments
Be aware when using the 'fm' syntax it will not include any values after the decimal place unless specified using zeros. For example:
SELECT TO_CHAR(12345, 'fm99,999.00') FROM dual
returns: '12,345.00'
SELECT TO_CHAR(12345, 'fm99,999.99') FROM dual
returns: '12,345.'
As you can see this would be an issue if you are expecting two zeros after the decimals place (maybe in fee reports for example).