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


SQL>

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

share|improve this question
add comment

1 Answer

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;

TO_CHAR(123.56,'0999.99') 
------------------------- 
 0123.56  

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
3  
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
add comment

Your Answer

 
discard

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.