• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3652
  • Last Modified:

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?

 

0
JelaV
Asked:
JelaV
  • 2
1 Solution
 
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
 
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

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now