compare varchar with number
497828Mar 21 2007 — edited Mar 23 2007Hi,
I had a problem as below:
Create table Test (col1 varchar2(20));
insert into Test (col1) values ('123');
insert into Test (col1) values ('ABC');
select * from Test Where col1 = 123;
I get following error:
ORA-01722: invalid number
select * from Test Where col1 = '123';
select successful.
But when I perform following step:
Delete from Test;
insert into Test (col1) values ('123');
insert into Test (col1) values ('234');
insert into Test (col1) values ('345');
select * from Test WHERE col1 = 123;
Select successful.
I just query why I can't compare 1 number to a varchar2 column when there is character record in that column. I'm using Oracle DB 10.2.0.2 and I believe oracle should be allow implicit convert between number char varchar2. Do I miss anything to make my first select statement return with the record I need?
Thank in advance.
Eng Khoon