Solved

How to compare number and varchar?

Posted on 2009-05-02
4
3,343 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]
Comment Utility
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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Hello All, In previous article we used Hortonworks sandbox to work with Hadoop. Now, lets think to create own single node Hadoop on Linux. Here we Install and Configure Apache Hadoop on UI based Oracle Linux. I assume, you have VMware installe…
Note: You must have administrative privileges in order to create/edit Roles. Salesforce.com (http://www.salesforce.com/) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales and marke…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

18 Experts available now in Live!

Get 1:1 Help Now