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.

Answer 1

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).