Thanks for the question, Priyanka.
Asked: March 13, 2018 - 6:39 am UTC
Last updated: March 13, 2018 - 12:30 pm UTC
Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Viewed 1000+ times
You Asked
I have a table that has two VARCHAR2 columns.
create table A
( char_col varchar2(200),
char_col2 varchar2(200),
num1 number(30));
insert into A values ('1,23,234','1,23,234','550');
insert into A values ('44,23,234','5,130','1280');
I am trying to do -
select to_number(char_col) - num1 from Aworks fine.
I am trying to do -
select to_number(char_col2) - num1 from dual and it fails with error - ORA-01722 - Invalid number
Why does this happen only for char_col2 when both the columns, char_col and char_col2 are varchar2 ?
and Connor said...
Sorry, I can't repeat your findings - it fails for both of mine
SQL> create table A
2 ( char_col varchar2(200),
3 char_col2 varchar2(200),
4 num1 number(30));
Table created.
SQL>
SQL> insert into A values ('1,23,234','1,23,234','550');
1 row created.
SQL> insert into A values ('44,23,234','5,130','1280');
1 row created.
SQL>
SQL> select to_number(char_col) - num1 from A;
select to_number(char_col) - num1 from A
*
ERROR at line 1:
ORA-01722: invalid number
SQL> select to_number(char_col2) - num1 from A;
select to_number(char_col2) - num1 from A
*
ERROR at line 1:
ORA-01722: invalid number