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.
SQL> select TO_NUMBER(123.56,'999.9') from dual;
select TO_NUMBER(123.56,'999.9') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number


SQL> select TO_CHAR(123.56,'999.9') from dual;

TO_CHA
------
 123.6

SQL>

I am having a hard time in understanding the nuances of Oracle SQL. For example, have a look at the two queries above.

Why does first query fail but second one succeed?

share|improve this question
add comment

1 Answer 1

up vote 5 down vote accepted

According to the documentation, the first value for a to_number needs to be

a BINARY_DOUBLE value or a value of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type containing a number in the format specified by the optional format model

Therefore Oracle is implicitly converting 123.56 into a character value. The problem then comes that the format for the to_number does not match the value being given. This can be seen because you get the same error for the following two variations:

select to_number(to_char(123.56),'999.9') from dual;

select to_number('123.56','999.9') from dual;

But you don't for this:

select to_number(123.56,'999.99') from dual;

When converting characters to numbers the format specification must match exactly, but when converting numbers to characters the number can be selectively extracted using the format specifier.

share|improve this answer
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.