Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

1 Answer 1

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

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.