Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am looking for result 0123.56 and the following query should have worked.

SQL> select to_number(123.56,'0999.99') from dual;
select to_number(123.56,'0999.99') from dual
ERROR at line 1:
ORA-01722: invalid number


But it doesn't. What am I doing wrong here?

share|improve this question
up vote 5 down vote accepted

It looks like you are trying to convert a number to a number - try to_char instead:

select to_char(123.56,'0999.99') from dual;


Note that in contrast, select to_number('0123.56','0999.99') from dual; will succeed because the format specification matches, but will not return the text 0123.56 as you want - the '0999.99' in this case is an input format, not an output format - the number 123.56 will be returned.

share|improve this answer
to_char worked! Strange though. Why does to_number not work this way? – Lazer Aug 24 '11 at 17:20
The result you are after is 0123.56, which is not a number (it is a string/char/varchar/text/whatever). to_number converts a text type (or a binary_double) to a number. It is best to try and separate in your mind the concept of a number and the various ways you can present that number visually (eg leading zeros, comma thousand seperators etc) - the visual representation is not a number, it is text. – Jack Douglas Aug 24 '11 at 17:34
The problem with this solution, is that it left-justifies the result column, since it is now a character column. :-( – cartbeforehorse Apr 21 at 12:59
@cartbeforehorse try to_char(123.56,'fm0999.99'), or perhaps you want to space pad on the left? – Jack Douglas Apr 21 at 21:41
@JackDouglas Actually, further investigation shows that it was the client-end software that was doing funny things with formatting. Different clients manage "text-which-is-really-a-number" in different ways. But your reference to the fm mask is a useful one all the same - thanks. Good call. – cartbeforehorse Apr 22 at 8:50

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.