How to compare number and varchar?

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.
Any solution?

 

JelaVAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I use this function:_
create or replace FUNCTION GetNumber2(txt_IN IN varchar2)
RETURN NUMBER
IS
	test NUMBER;
	res VARCHAR2(128);
BEGIN
 
BEGIN
  res := TRANSLATE ( txt_IN, '0123456789' , '          ');
  res := REPLACE( TRANSLATE ( txt_IN, res ,  ' ' ) , ' ', '');
 
  test := TO_NUMBER(res);
 
EXCEPTION
  WHEN VALUE_ERROR THEN test:=NULL;
END;
 
RETURN (test);
 
END GetNumber2;

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about:
 select * from a, b where TRIM(LEADING '0' FROM a.col1) = TO_CHAR(b.col1)

Open in new window

0
 
JelaVAuthor Commented:
So the question is: How to get numeric values from an alphanumeric column?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.