Forum Stats

  • 3,724,400 Users
  • 2,244,749 Discussions
  • 7,850,995 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

ORA-01722: invalid number

user650888
user650888 Member Posts: 490 Bronze Badge
edited November 2009 in SQL & PL/SQL
create  table tab_varc(user_data_15 varchar2(15))

create table tab_wf (loan_num number)

insert into tab_wf values (2)
/
insert into tab_varc values ('2')

/
insert into tab_varc values ('#')
/
commit
/


select a.USER_DATA_15
from tab_varc a
where a.USER_DATA_15 in (select loan_num from tab_wf) 
when i give the above select, i get ORA-01722 error, the data
in tab_varc is bad, and it has millions of rows, so I do not know
how to find out the bad data

is there a way to make the above query work?

i tried this
select a.USER_DATA_15
from tab_varc a
where to_number(a.USER_DATA_15) in (select loan_num from tab_wf) 
it did not work

Answers

  • Centinul
    Centinul Member Posts: 6,871
    edited November 2009
    There are multiply ways to approach this, but the first order of business is to eliminate the bad number data. You could do this using regular expressions or a custom function. The example below uses a custom function defined as follows:
    CREATE OR REPLACE FUNCTION NUMBER_TEST
    (
    	pNum	IN VARCHAR2
    )
    RETURN NUMBER
    AS
    BEGIN
    	RETURN TO_NUMBER(pNum);
    EXCEPTION
    	WHEN OTHERS THEN
    		RETURN NULL;
    END;
    /
    If the value passed to the function cannot be converted to a number than NULL is returned.

    Using this function you could run the following query:
    SELECT	USER_DATA_15
    FROM
    (
    	SELECT 	USER_DATA_15
    	FROM	TAB_VARC
    	WHERE	NUMBER_TEST(USER_DATA_15) IS NOT NULL /* Replace with REGEXP_* check */
    )
    WHERE	USER_DATA_15 IN (SELECT LOAN_NUM FROM TAB_WF);
    The reason I used a subquery is because we need to filter out the non-number values first. Oracle can evaluate a predicate in any order so that is why we have to force it to be evaluated first with a subquery.

    As I mentioned above you could replace this check with regular expressions as well.

    HTH!

    Edited by: Centinul on Nov 19, 2009 8:29 PM

    This may not work if Oracle chooses to push the predicate into the subquery
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,215 Red Diamond
    edited November 2009
    Hi,

    A lot of folks use NUMBER columns to store numbers; this is one of the reasons why.

    Assuming the numbers are all unsigned integers, you can find the bad data this way:
    SELECT	*	-- Or whatever columns you want
    FROM	tab_varc
    WHERE	LTRIM ( user_data_15
    	      , '0123456789'
    	      )		IS NOT NULL
    ;
    This will find any row where user_data_15 includes anything at all besides digits: signs, decimal points, and spaces are all treated as non-numeric.
    If that's a problem, [this thread|http://forums.oracle.com/forums/thread.jspa?threadID=925130] has a function, to_num, that can help you.

    You can avoid the ORA-01722 error by converting the NUMBER column to a VARCHAR2, like this:
    select 	a.USER_DATA_15
    from 	tab_varc 	a
    where 	a.USER_DATA_15 in ( select  TO_CHAR (loan_num )
    			    from    tab_wf
    			  ) 
    If user_data_15 is not the shortest possible way to represent loan_num, it will not be consered a match.

    For example, the following strings are all valid ways to represent the number 4:
    4.00000
    4.
         4
    +4
    00004
    but none of them are the same as TO_CHAR (4), which is the one-character string '4'.
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    The reason I used a subquery is because we need to filter out the non-number values first. Oracle can evaluate a predicate in any order so that is why we have to force it to be evaluated first with a subquery.
    While it is true that you have to force Oracle to filter out the non-numeric data before comparing it to the LOAN_NUM, merely putting the predicate in a subquery does not guarantee that Oracle applies the filter condition first. I was shocked to learn this a few years ago-- Jonathan Gennick has a great article [Subquery Madness|http://www.gennick.com/madness.html] that goes into more detail.

    Justin
  • Centinul
    Centinul Member Posts: 6,871
    edited November 2009
    Justin Cave wrote:
    The reason I used a subquery is because we need to filter out the non-number values first. Oracle can evaluate a predicate in any order so that is why we have to force it to be evaluated first with a subquery.
    While it is true that you have to force Oracle to filter out the non-numeric data before comparing it to the LOAN_NUM, merely putting the predicate in a subquery does not guarantee that Oracle applies the filter condition first. I was shocked to learn this a few years ago-- Jonathan Gennick has a great article [Subquery Madness|http://www.gennick.com/madness.html] that goes into more detail.

    Justin
    Bah foiled again... :)

    I need to stop responding first to posts. I feel like I always make mistakes.

    That article was an enlightening and amusing read, and I appreciate that you posted it. The only true solution is to store number data in number fields.

    Edited by: Centinul on Nov 19, 2009 8:26 PM
  • Lakmal Rajapakse
    Lakmal Rajapakse Member Posts: 827
    edited November 2009
    Firstly if you data is supposed to be numerical then it should not be stored as a varchar, then you will not face this situation.

    However your query will work if you do it like thats assuming user_data_15 does not have left or right padded 0s:
    select a.USER_DATA_15
    from tab_varc a
    where trim(a.USER_DATA_15) in (select to_char(loan_num) from tab_wf)
    To find non-numerical values in a column you could use the following query:
    select user_data_15
    from tab_varc
    where regexp_instr(trim(user_data_15), '^[-+]?[0-9]*\.?[0-9]+$') = 0
    Edited by: Lakmal Rajapakse on 19-Nov-2009 17:28
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    Wow.. ;)

    That is really wonderful post. Learned really priceless piece of information. :)

    Thanks a ton Justin. ;)

    Regards.

    Satyaki De.
This discussion has been closed.