Я пытаюсь найти некоторые проблемные записи в очень большой таблице Oracle. Столбец должен содержать все числовые данные, даже если это столбец varchar2. Мне нужно найти записи, которые не содержат числовых данных (функция to_number (col_name) вызывает ошибку, когда я пытаюсь вызвать ее в этом столбце).
Поиск строк, которые не содержат числовых данных в Oracle
Ответ 1
Я думал, что вы можете использовать условие regexp_like и использовать регулярное выражение для поиска любых нечисловых чисел. Надеюсь, это поможет?!
SELECT * FROM table_with_column_to_search WHERE REGEXP_LIKE(varchar_col_with_non_numerics, '[^0-9]+');
Ответ 2
Чтобы получить индикатор:
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", это будет просто проигнорировано.
Ответ 3
В отличие от ответа SGB, я предпочитаю делать регулярное выражение, определяющее фактический формат моих данных и отрицая это. Это позволяет мне определять такие значения, как $DDD, DDD, DDD.DD В простом сценарии OPs это будет выглядеть как
SELECT *
FROM table_with_column_to_search
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^[0-9]+$');
который находит все неположительные целые числа. Если вы также принимаете целые числа negatiuve, это простое изменение, просто добавьте необязательный главный минус.
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 был бы немного сложнее сделать, если вы хотите больше, чем просто положительные целые числа.
Ответ 4
Из http://www.dba-oracle.com/t_isnumeric.htm
LENGTH(TRIM(TRANSLATE(, ' +-.0123456789', ' '))) is null
Если в строке после TRIM осталось что-либо, это должны быть нечисловые символы.
Ответ 5
Используйте этот
SELECT * FROM TableToSearch WHERE NOT REGEXP_LIKE (ColumnToSearch, '^ -? [0-9] + (. [0-9] +)? $');
Ответ 6
Я нашел это полезным:
select translate('your string','_0123456789','_') from dual
Если результат равен NULL, это числовое значение (игнорирование чисел с плавающей запятой).
Однако, я немного озадачен, почему подчеркивание необходимо. Без него следующее также возвращает null:
select translate('s123','0123456789', '') from dual
Существует также один из моих любимых трюков - не идеально, если строка содержит такие вещи, как "*" или "#":
SELECT 'is a number' FROM dual WHERE UPPER('123') = LOWER('123')
Ответ 7
После некоторого тестирования, основываясь на предложениях в предыдущих ответах, кажется, есть два доступных решения.
Метод 1 является самым быстрым, но менее мощным с точки зрения соответствия более сложных моделей.
Метод 2 более гибкий, но медленнее.
Метод 1 - быстрый
Я проверил этот метод на таблице с 1 миллионом строк.
Это, кажется, в 3,8 раза быстрее, чем регулярные выражения.
0-замена решает проблему, что 0 сопоставляется с пробелом и, похоже, не замедляет запрос.
SELECT *
FROM <table>
WHERE TRANSLATE(replace(<char_column>,'0',''),'0123456789',' ') IS NOT NULL;
Метод 2 - медленнее, но более гибко
Я сравнивал скорость отрицания внутри или вне выражения регулярного выражения. Оба одинаково медленнее, чем перевод. В результате подход @ciuly кажется наиболее разумным при использовании регулярного выражения.
SELECT *
FROM <table>
WHERE NOT REGEXP_LIKE(<char_column>, '^[0-9]+$');
Ответ 8
Вы можете использовать эту проверку:
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;
Ответ 9
После некоторого тестирования я придумал это решение, дайте мне знать, если это поможет.
Добавьте это ниже 2 условия в свой запрос и найдете записи, которые не содержат числовые данные
и REGEXP_LIKE (, '\ D') - это выбор не числовых данных и не REGEXP_LIKE (column_name, '^ [-] {1}\d {1}') - это отбрасывает отрицательные (-) значения