4

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?

8

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.

5
  • 1
    to_char worked! Strange though. Why does to_number not work this way?
    – Lazer
    Aug 24 '11 at 17:20
  • 4
    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. 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. :-( Apr 21 '16 at 12:59
  • 2
    @cartbeforehorse try to_char(123.56,'fm0999.99'), or perhaps you want to space pad on the left? Apr 21 '16 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. Apr 22 '16 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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