Поиск строк, которые не содержат числовые данные в Oracle
Я пытаюсь найти некоторые проблемные записи в очень большой таблице Oracle. Столбец должен содержать все числовые данные, даже если это столбец varchar2. Мне нужно найти записи, которые не содержат числовых данных (функция to_number(col_name) выдает ошибку, когда я пытаюсь вызвать ее в этом столбце).
11 ответов
Я думал, что вы могли бы использовать условие regexp_like и использовать регулярное выражение, чтобы найти любые нечисловые значения. Я надеюсь, что это может помочь?!
SELECT * FROM table_with_column_to_search WHERE REGEXP_LIKE(varchar_col_with_non_numerics, '[^0-9]+');
Чтобы получить индикатор:
DECODE( TRANSLATE(your_number,' 0123456789',' ')
например
SQL> select DECODE( TRANSLATE('12345zzz_not_numberee',' 0123456789',' '), NULL, 'number','contains char')
2 from dual
3 /
"contains char"
а также
SQL> select DECODE( TRANSLATE('12345',' 0123456789',' '), NULL, 'number','contains char')
2 from dual
3 /
"number"
а также
SQL> select DECODE( TRANSLATE('123405',' 0123456789',' '), NULL, 'number','contains char')
2 from dual
3 /
"number"
Oracle 11g имеет регулярные выражения, так что вы можете использовать это, чтобы получить фактическое число:
SQL> SELECT colA
2 FROM t1
3 WHERE REGEXP_LIKE(colA, '[[:digit:]]');
COL1
----------
47845
48543
12
...
Если есть нечисловое значение, например "23g", оно будет просто проигнорировано.
В отличие от ответа SGB, я предпочитаю использовать регулярное выражение, определяющее фактический формат моих данных и отрицающее его. Это позволяет мне определять такие значения, как $DDD,DDD,DDD.DD. В простом сценарии OP это будет выглядеть так:
SELECT *
FROM table_with_column_to_search
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^[0-9]+$');
который находит все неположительные целые числа. Если вы также принимаете отрицательные целые числа, это легко изменить, просто добавьте дополнительный начальный минус.
SELECT *
FROM table_with_column_to_search
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^-?[0-9]+$');
принимая плавающие точки...
SELECT *
FROM table_with_column_to_search
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^-?[0-9]+(\.[0-9]+)?$');
То же самое идет дальше с любым форматом. Обычно у вас уже есть форматы для проверки входных данных, поэтому, когда вы захотите найти данные, которые не соответствуют этому формату... проще отрицать этот формат, чем придумывать другой; что в случае подхода SGB было бы немного сложнее, если вы хотите больше, чем просто положительные целые числа.
Использовать этот
SELECT *
FROM TableToSearch
WHERE NOT REGEXP_LIKE(ColumnToSearch, '^-?[0-9]+(\.[0-9]+)?$');
После некоторого тестирования я придумал это решение, дайте мне знать, если оно поможет.
Добавьте это ниже 2 условий в ваш запрос, и он найдет записи, которые не содержат числовые данные
and REGEXP_LIKE(<column_name>, '\D') -- this selects non numeric data
and not REGEXP_LIKE(column_name,'^[-]{1}\d{1}') -- this filters out negative(-) values
Начиная с Oracle 12.2 функция to_number имеет опцию
ON CONVERSION ERRORпредложение, которое может перехватить исключение и предоставить значение по умолчанию.
Это можно использовать для проверки числовых значений. Простой набор
NULLкогда преобразование завершается ошибкой и фильтрует все значения, отличные от NULL .
Пример
with num as (
select '123' vc_col from dual union all
select '1,23' from dual union all
select 'RV12P2000' from dual union all
select null from dual)
select
vc_col
from num
where /* filter numbers */
vc_col is not null and
to_number(vc_col DEFAULT NULL ON CONVERSION ERROR) is not null
;
VC_COL
---------
123
1,23
С http://www.dba-oracle.com/t_isnumeric.htm
LENGTH(TRIM(TRANSLATE(, ' +-.0123456789', ' '))) is null
Если после строки TRIM что-то осталось в строке, это должны быть нечисловые символы.
Вы можете использовать эту одну проверку:
create or replace function to_n(c varchar2) return number is
begin return to_number(c);
exception when others then return -123456;
end;
select id, n from t where to_n(n) = -123456;
После некоторого тестирования, основанного на предложениях из предыдущих ответов, кажется, есть два полезных решения.
Метод 1 является самым быстрым, но менее мощным с точки зрения соответствия более сложным шаблонам.
Метод 2 более гибкий, но медленный.
Способ 1 - самый быстрый
Я проверил этот метод на таблице с 1 миллионом строк.
Кажется, в 3,8 раза быстрее, чем решения регулярных выражений.
Замена 0 решает проблему с отображением 0 в пробел и, по-видимому, не замедляет запрос.
SELECT *
FROM <table>
WHERE TRANSLATE(replace(<char_column>,'0',''),'0123456789',' ') IS NOT NULL;
Способ 2 - медленнее, но более гибкий
Я сравнил скорость помещения отрицания внутри или вне выражения регулярного выражения. Оба одинаково медленнее, чем translate-решение. В результате подход @ciuly кажется наиболее разумным при использовании регулярных выражений.
SELECT *
FROM <table>
WHERE NOT REGEXP_LIKE(<char_column>, '^[0-9]+$');
Я упорядочиваю лотки по проблемному столбцу и нахожу строки с столбцом.
SELECT
D.UNIT_CODE,
D.CUATM,
D.CAPITOL,
D.RIND,
D.COL1 AS COL1
FROM
VW_DATA_ALL_GC D
WHERE
(D.PERIOADA IN (:pPERIOADA)) AND
(D.FORM = 62)
AND D.COL1 IS NOT NULL
-- AND REGEXP_LIKE (D.COL1, '\[\[:alpha:\]\]')
-- AND REGEXP_LIKE(D.COL1, '\[\[:digit:\]\]')
--AND REGEXP_LIKE(TO_CHAR(D.COL1), '\[^0-9\]+')
GROUP BY
D.UNIT_CODE,
D.CUATM,
D.CAPITOL,
D.RIND ,
D.COL1
ORDER BY
D.COL1
Я нашел это полезным:
select translate('your string','_0123456789','_') from dual
Если результат равен NULL, он числовой (игнорируя числа с плавающей запятой.)
Тем не менее, я немного сбит с толку, почему подчеркивание необходимо. Без этого следующее также возвращает нуль:
select translate('s123','0123456789', '') from dual
Есть также один из моих любимых приемов - не идеален, если строка содержит такие вещи, как "*" или "#":
SELECT 'is a number' FROM dual WHERE UPPER('123') = LOWER('123')