Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

to_char vs to_number

user10913793Apr 11 2019 — edited Apr 20 2019

Hello,

I have gone through to_number and to_char  functions and have few doubts:

Example 1:

SQL> select to_char(123.56, '999.99') from dual. The format mask exactly matches the number so output is as expected i.e. 123.56

Example 2:

SQL> select to_char(123.56, '99.99') from dual

TO_CHA
------
######

Format mask is smaller than the number being converted, so a string of hash symbols is returned, as expected.

Example 3:

SQL> select to_number(123.56, '99.99') from dual;

select to_number(123.56, '99.99') from dual

                 *

ERROR at line 1:

ORA-01722: invalid number

In example 3 ,to_number exactly matches the format  with the number being converted, and format mask is smaller than the number being converted, so  why we get an error but why an 'Invalid number' error  and not string of hash symbols?

Example 4:

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

TO_CHA
------
123.6

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

Please explain the difference in working of to_char vs to_number. in Example 4

Comments
Post Details
Added on Apr 11 2019
12 comments
4,053 views