Oracle ORA-01722: неверный номер Ошибка из-за условия WHERE IS NULL

У меня есть следующий SQL:

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

Предполагается, что запрос отфильтровывает нечисловые имена из таблицы A, проверяя, не осталось ли ничего, если все цифры заменены ничем.

Может кто-нибудь объяснить, почему я получаю исключение «Недопустимый номер», когда добавляю следующее условие WHERE в качестве внешнего WHERE:

WHERE MyNumber IS NULL

Насколько это актуально, какой тип MyNumber? В частности, я также хотел бы знать, почему я НЕ получаю ошибку, когда я отрицаю условие:

WHERE NOT MyNumber IS NULL

Заранее благодарю за любую помощь.


sql oracle isnull
person Xardestro    schedule 10.07.2018    source источник
comment
Если вы используете Oracle 12cR2, то DEFAULT ...ON CONVERSION ERROR например здесь   -  person Lukasz Szozda    schedule 10.07.2018
comment
Итак, первый запрос работает нормально? (to_number() не выдает ошибки).   -  person Error_2646    schedule 10.07.2018
comment
Просто совет: вместо того, чтобы использовать для этого REGEXP_REPLACE, вы, вероятно, предпочтете использовать REGEXP_LIKE.   -  person Mr. Llama    schedule 10.07.2018

Ответы (2)


arrow_upward
2
arrow_downward

Я могу воспроизвести то, что видите вы, но получаю сообщение об ошибке с is null или 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

Возможно, вы сможете добавить подсказки, чтобы заставить его обрабатывать его по-другому, но вместо этого вы можете добавить другое регулярное выражение, чтобы любые нечисловые значения, которые действительно попадают в преобразование, не вызывали проблемы:

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

Как отметил @MrLlama, это было бы немного чище с regexp_like:

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

который получает те же результаты (включая ваши исходные ошибки).

person Alex Poole    schedule 10.07.2018

arrow_upward
0
arrow_downward

Предполагается, что запрос отфильтровывает нечисловые имена из таблицы A.

Почему бы вам не использовать что-то подобное вместо этого?

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>

Это может потребовать некоторой корректировки, в зависимости от того, что действительно содержит столбец NAME.

person Littlefoot    schedule 10.07.2018