Oracle ORA-01722: invalid number Error because of WHERE IS NULL Condition

Question

I have the following SQL:

Select MyNumber
FROM (SELECT to_number(Name) AS MyNumber
    FROM TableA
    WHERE regexp_replace(Name, '\d+') IS NULL)

The Query is supposed to filter out non-numeric Names from TableA by checking if nothing is left, if all Digits are replaced with nothing.

Can someone explain why I get an "Invalid Number"-Exception when I add the following WHERE Condition as an outer WHERE:

WHERE MyNumber IS NULL

How is it relevant, what type MyNumber is? In particular, I would also like to know, why I DO NOT get an error, when I negate the condition:

WHERE NOT MyNumber IS NULL

Thanks in advance for any help.

If you are using Oracle 12cR2 then `DEFAULT ...ON CONVERSION ERROR` like [here](https://stackoverflow.com/a/45886745/5070879) — Lukasz Szozda, Jul 10 '18 at 16:20
So the first query runs just fine? (to_number() doesn't error out). — Error_2646, Jul 10 '18 at 16:47
Just a tip, instead of using `REGEXP_REPLACE` for this, you'd probably rather use [`REGEXP_LIKE`](https://docs.oracle.com/database/121/SQLRF/conditions007.htm#SQLRF00501). — Mr. Llama, Jul 10 '18 at 17:19

Alex Poole · Answer 1 · 2018-07-10T19:27:22.540

I can sort of replicate what you're seeing, but I get the error with is null or is not null:

create table tablea (name) as
select '123' from dual
union all select 'abc123' from dual
union all select 'abc123def456,' from dual
union all select '1abc123def456,' from dual;

SELECT MyNumber
FROM (
  SELECT to_number(Name) AS MyNumber
  FROM TableA
  WHERE regexp_replace(Name, '\d+') IS NULL
);

  MYNUMBER
----------
       123

SELECT MyNumber
FROM (
  SELECT to_number(Name) AS MyNumber
  FROM TableA
  WHERE regexp_replace(Name, '\d+') IS NULL
)
WHERE mynumber IS NULL;

ORA-01722: invalid number

SELECT MyNumber
FROM (
  SELECT to_number(Name) AS MyNumber
  FROM TableA
  WHERE regexp_replace(Name, '\d+') IS NULL
)
WHERE mynumber IS NOT NULL;

ORA-01722: invalid number

You might be able to add hints to make it process it differently, but you could instead add another regex so any non-numeric values that do hit the conversion don't cause a problem:

SELECT MyNumber
FROM (
  SELECT to_number(regexp_replace(Name, '[^[:digit:]]')) AS MyNumber
  FROM TableA
  WHERE regexp_replace(Name, '[[:digit:]]+') IS NULL
);

  MYNUMBER
----------
       123

SELECT MyNumber
FROM (
  SELECT to_number(regexp_replace(Name, '[^[:digit:]]')) AS MyNumber
  FROM TableA
  WHERE regexp_replace(Name, '[[:digit:]]+') IS NULL
)
WHERE mynumber IS NULL;

no rows selected

SELECT MyNumber
FROM (
  SELECT to_number(regexp_replace(Name, '[^[:digit:]]')) AS MyNumber
  FROM TableA
  WHERE regexp_replace(Name, '[[:digit:]]+') IS NULL
)
WHERE mynumber IS NOT NULL;

  MYNUMBER
----------
       123

As @MrLlama pointed out, this would be a bit cleaner with regexp_like:

SELECT MyNumber
FROM (
  SELECT to_number(regexp_replace(Name, '[^[:digit:]]')) AS MyNumber
  FROM TableA
  WHERE regexp_like(Name, '^[[:digit:]]+$')
);

which gets the same results (including your original errors).

score 0 · Answer 2 · answered Jul 10 '18 at 20:18

Query is supposed to filter out non-numeric Names from TableA

Why wouldn't you use something like this, instead?

SQL> with test (name) as
  2    (select '12345'     from dual union
  3     select 'abc123'    from dual union
  4     select 'lksfj'     from dual union
  5     select 'ping pong' from dual
  6    )
  7  select name
  8  from test
  9  where regexp_like(name, '^\d+$');

NAME
---------
12345

SQL>

This might need some adjustment, depending on what NAME column really contains.

Oracle ORA-01722: invalid number Error because of WHERE IS NULL Condition

2 Answers2