Solved

How to compare number and varchar?

Posted on 2009-05-02
4
3,366 Views
Last Modified: 2012-06-22
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
Comment
Question by:JelaV
  • 2
4 Comments
 
LVL 142

Expert Comment

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

Open in new window

0
 

Author Comment

by:JelaV
ID: 24292982
So the question is: How to get numeric values from an alphanumeric column?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24293098
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have problems displaying reports in CRM after a new installation, review the following steps: Check that SQL Server Reporting Services is working On the server that has SQL Server Reporting Services (SQL SRS) installed, check that the S…
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now