Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to compare number and varchar?

Posted on 2009-05-02
4
3,405 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 143

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 143

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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

I showed you how to use console view (HERE (http://www.experts-exchange.com/articles/18379/Getting-Started-and-Using-the-Salesforce-com-Console.html)) -– but how do you set it up on the admin side of Salesforce? Note that you have to have Admin leve…
Salesforce.com is a cloud-based customer relationship management (CRM) system. In this article, you will learn how to add and map custom lead and contact fields to your Salesforce instance.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

839 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