How to compare number and varchar?
Posted on 2009-05-02
Hi - I have table 'a' with a NUMBER column (col1) that is of course always digits. The other table 'b' has the same column (col1) that is VARCHAR2 of 10 chars.
The b.col1 could be: '', ' ', 'abc', '1234', '02345'...
The a.col1 could be: 1234, 0123, 02345.....
I need to select from these two tables where a.col1 = b.col1:
select * from a, b where a.col1= b.col1
1. If I use to_char(a.col1) it would give me 2345 instead of 02345.
So select * from a, b where to_char(a.col1)= trim(b.col1) would not return what I want
2. select * from a, b where a.col1= trim(b.col1) would return the error invalid number as
'abs' is not a number.
So I do need to get only numbers from the columns b.col1.