ORA-01722: invalid number where clause

Question

when I want to compare these two columns it shows me this menssage, without the "where clause" there isnt any error, but the error shows up when I write the "Where"

This is my sql sentences:

select MONEY
       , to_char(MONEY, 'FM999G999G999G999D99', 'NLS_NUMERIC_CHARACTERS='',.''')  
from ACOUNT
where MONEY is not null

menssage error: ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number.

also ,do you have any virtual columns? please attach table structure as well — Derviş Kayımbaşıoğlu, Sep 29 '20 at 08:06

VBoka · Accepted Answer · 2020-09-29T08:25:57.633

You have data in your table ACOUNT in the column MONEY that is not numeric.

Here is a demo:

DEMO

I assume that the reason why it is working when there is no where clause is because your tool is showing you only first 50 results... And when you add where clause and filter the data, then in your first 50 results there is a data that is not numeric.

You can change this in your tool by going to : Tools >> Preferences >> Database >> Advanced.

You can create this function:

CREATE OR REPLACE FUNCTION is_number (p_str IN VARCHAR2)
  RETURN NUMBER
IS
  l_num NUMBER;
BEGIN
  l_num := to_number(p_str);
  RETURN l_num;

EXCEPTION
  WHEN others THEN
    RETURN NULL;
END is_number;
/

That I have founded here: How does numeric comparison on Oracle VARCHAR column work?

And then with it you can use this code:

select *
from (select MONEY_c
             , to_char(is_number(MONEY_c), 'FM999G999G999G999D99', 'NLS_NUMERIC_CHARACTERS='',.''')  as money_2
      from ACOUNT)
where money_2 is not null

I don't think the variable is doing anything useful, although perhaps the code is easier to understand with it there. Also I'd consider declaring it `parallel_enable`, and including [`pragma udf;`](https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/UDF-pragma.html). — William Robertson, Sep 29 '20 at 13:17

score 0 · Answer 2 · answered Sep 29 '20 at 08:35

It might be not only non-numeric character, but just a comma separated "numeric"-string value

this example causes the same error

select to_char('2,5', 'FM999G999G999G999D99', 'NLS_NUMERIC_CHARACTERS='',.''') from dual;

So, here is the query to find problem records in the ACOUNT table.

select * from ACOUNT where not regexp_like(replace(MONEY, '.', ''),'^[0-9]+$');
-- replace() is here to make regexp_like to ignore values like '2.5' which can be converted to a number

ORA-01722: invalid number where clause

2 Answers2