Проверьте, есть ли функция "это число" в Oracle

Я пытаюсь проверить, является ли значение из столбца запроса оракула (10g) числом, чтобы сравнить его. Что-то вроде:

select case when ( is_number(myTable.id) and (myTable.id >0) ) 
            then 'Is a number greater than 0' 
            else 'it is not a number' 
       end as valuetype  
  from table myTable

Любые идеи о том, как проверить это?

Ответ 1

Предполагая, что столбец идентификатора в myTable не объявлен как NUMBER (который кажется нечетным выбором и может быть проблематичным), вы можете написать функцию, которая пытается преобразовать ID (предположительно VARCHAR2) в число, выхватывает исключение и возвращает "Y" или "N". Что-то вроде

CREATE OR REPLACE FUNCTION is_number( p_str IN VARCHAR2 )
  RETURN VARCHAR2 DETERMINISTIC PARALLEL_ENABLE
IS
  l_num NUMBER;
BEGIN
  l_num := to_number( p_str );
  RETURN 'Y';
EXCEPTION
  WHEN value_error THEN
    RETURN 'N';
END is_number;

Затем вы можете вставить этот вызов в запрос, т.е.

SELECT (CASE WHEN is_number( myTable.id ) = 'Y' AND myTable.id > 0 
               THEN 'Number > 0'
             ELSE 'Something else'
         END) some_alias
  FROM myTable

Обратите внимание, что хотя PL/SQL имеет тип данных типа boolean, SQL этого не делает. Поэтому, когда вы можете объявить функцию, возвращающую логическое значение, вы не можете использовать такую функцию в SQL-запросе.

Ответ 2

Еще одна идея, упомянутая здесь - использовать регулярное выражение для проверки:

SELECT  foo 
FROM    bar
WHERE   REGEXP_LIKE (foo,'^[[:digit:]]+$');

Приятная часть - вам не нужна отдельная функция PL/SQL. Потенциально проблематичная часть состоит в том, что регулярное выражение может быть не самым эффективным методом для большого количества строк.

Ответ 3

Ответ Saish с помощью REGEXP_LIKE - правильная идея, но не поддерживает плавающие числа. Этот будет...

Возвращаемые значения, которые являются числовыми

SELECT  foo 
FROM    bar
WHERE   REGEXP_LIKE (foo,'^-?\d+(\.\d+)?$');

Возвращаемые значения не являются числовыми

SELECT  foo 
FROM    bar
WHERE   NOT REGEXP_LIKE (foo,'^-?\d+(\.\d+)?$');

Вы можете сами тестировать свои регулярные выражения, пока ваше сердце не будет удовлетворено http://regexpal.com/ (но убедитесь, что вы выбрали флажок match при разрывах строки для этого).

Ответ 4

Вы можете использовать функцию регулярного выражения 'regexp_like' в ORACLE (10g), как показано ниже:

select case
       when regexp_like(myTable.id, '[[:digit:]]') then
        case
       when myTable.id > 0 then
        'Is a number greater than 0'
       else
        'Is a number less than or equal to 0'
     end else 'it is not a number' end as valuetype
from table myTable

Ответ 5

Это потенциальный дубликат Поиск строк, которые не содержат числовых данных в Oracle. Также см.: Как определить, является ли строка числовой в SQL?.

Здесь представлено решение на основе Майкла Дарранта, которое работает для целых чисел.

SELECT foo
FROM bar
WHERE DECODE(TRIM(TRANSLATE(your_number,'0123456789',' ')), NULL, 'number','contains char') = 'number'

Адриан Карнейро опубликовал решение, которое работает для десятичных знаков и других. Однако, как указал Джастин Кейв, это будет неправильно классифицировать строки типа "123.45.23.234" или "131 + 234".

SELECT foo
FROM bar
WHERE DECODE(TRIM(TRANSLATE(your_number,'+-.0123456789',' ')), NULL, 'number','contains char') = 'number'

Если вам нужно решение без PL/SQL или REGEXP_LIKE, это может помочь.

Ответ 6

Я против использования when others, поэтому я бы использовал (возвращая "логическое целое" из-за того, что SQL не поддерживал логические значения)

create or replace function is_number(param in varchar2) return integer
 is
   ret number;
 begin
    ret := to_number(param);
    return 1; --true
 exception
    when invalid_number then return 0;
 end;

В вызове SQL вы должны использовать что-то вроде

select case when ( is_number(myTable.id)=1 and (myTable.id >'0') ) 
            then 'Is a number greater than 0' 
            else 'it is not a number or is not greater than 0' 
       end as valuetype  
  from table myTable

Ответ 7

Как определяется столбец? Если это поле varchar, то это не число (или сохраненное как одно). Oracle может выполнить преобразование для вас (например, выберите * from someTable, где charField = 0), но он будет возвращать только строки, где преобразование имеет значение true и возможно. Это также далека от идеальной ситуации, разумной.

Итак, если вы хотите сделать сравнение чисел и обработать этот столбец как число, возможно, его нужно определить как число?

Итак, вот что вы можете сделать:

create or replace function myToNumber(i_val in varchar2) return number is
 v_num number;
begin
 begin
   select to_number(i_val) into v_num from dual;
 exception
   when invalid_number then
   return null;
 end;
 return v_num;
end;

Вы также можете указать другие параметры, которые имеют обычный номер_объекта. Используйте так:

select * from someTable where myToNumber(someCharField) > 0;

Он не будет возвращать строки, которые Oracle видит как недопустимый номер.

Приветствия.

Ответ 8

CREATE OR REPLACE FUNCTION is_number(N IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    RETURN CASE regexp_like(N,'^[\+\-]?[0-9]*\.?[0-9]+$') WHEN TRUE THEN 1 ELSE 0 END;
END is_number;

Обратите внимание, что он не будет рассматривать число 45e4 как число, но вы всегда можете изменить регулярное выражение, чтобы выполнить противоположное.

Ответ 9

@JustinCave - замена "when value_error" для "когда другие" - это хорошая утонченность вашего подхода выше. Эта небольшая дополнительная настройка, хотя и концептуально то же самое, устраняет требование определения и последующего выделения памяти вашей переменной l_num:

function validNumber(vSomeValue IN varchar2)
     return varchar2 DETERMINISTIC PARALLEL_ENABLE
     is
begin
  return case when abs(vSomeValue) >= 0 then 'T' end;
exception
  when value_error then
    return 'F';
end;

Просто обратите внимание также на тех, кто предпочитает подражать логике формата Oracle с использованием "более рискованного" подхода REGEXP, пожалуйста, не забудьте рассмотреть NLS_NUMERIC_CHARACTERS и NLS_TERRITORY.

Ответ 10

Это мой запрос, чтобы найти все те, которые НЕ являются цифрами:

Select myVarcharField
From myTable
where not REGEXP_LIKE(myVarcharField, '^(-)?\d+(\.\d+)?$', '')
and not REGEXP_LIKE(myVarcharField, '^(-)?\d+(\,\d+)?$', '');

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

Ответ 11

ну, вы можете создать функцию is_number для вызова, чтобы ваш код работал.

create or replace function is_number(param varchar2) return boolean
 as
   ret number;
 begin
    ret := to_number(param);
    return true;
 exception
    when others then return false;
 end;

EDIT: Пожалуйста, отложите ответ Джастину. Забыл эту небольшую деталь для чистого вызова SQL....

Ответ 12

Функция для мобильного номера длиной 10 цифр и начиная с 9,8,7 с использованием регулярного выражения

create or replace FUNCTION VALIDATE_MOBILE_NUMBER
(   
   "MOBILE_NUMBER" IN varchar2
)
RETURN varchar2
IS
  v_result varchar2(10);

BEGIN
    CASE
    WHEN length(MOBILE_NUMBER) = 10 
    AND MOBILE_NUMBER IS NOT NULL
    AND REGEXP_LIKE(MOBILE_NUMBER, '^[0-9]+$')
    AND MOBILE_NUMBER Like '9%' OR MOBILE_NUMBER Like '8%' OR MOBILE_NUMBER Like '7%'
    then 
    v_result := 'valid';
    RETURN v_result;
      else 
      v_result := 'invalid';
       RETURN v_result;
       end case;
    END;

Ответ 13

Я считаю, что следующее решение (основанное на вышеприведенном подходе regexp) является оптимальным:

function isInteger(vYourValue IN varchar2)
         return varchar2
         is
begin
  return case REGEXP_INSTR(vYourValue,'^[[:digit:]]+$') when 0 then 'F' else 'T' end;
end;

Почему я говорю это?

Проверяемое числовое подмножество может быть изменено по желанию, соответствующим образом изменяя регулярное выражение.
Он может использоваться в SQL, т.е. select isInteger (myCol) из mytable; поскольку он возвращает 'T' 'F' вместо boolean.
Его можно использовать изначально в pl/sql, т.е. if isInteger (vMyValue) = 'T', тогда....
Он удовлетворяет утверждению чистоты WNDS, WNPS.
Не полагается, на мой взгляд, слишком широкий спектр "когда другие" подходят для определения результата.

Ответ 14

Обратите внимание, что подходы регулярного выражения или функции несколько раз медленнее, чем обычное условие sql.

Таким образом, некоторые эвристические обходные пути с ограниченной применимостью делают для огромных сканирований.

Существует решение для случаев, когда вы точно знаете, что нечисловые значения будут содержать некоторые буквенные буквы:

select case when upper(dummy)=lower(dummy) then '~numeric' else '~alpabetic' end from dual

И если вы знаете, что какая-то буква всегда будет присутствовать в нечисловых случаях:

select case when instr(dummy, 'X')>0 then '~alpabetic' else '~numeric' end from dual

Когда числовые случаи всегда будут содержать ноль:

select case when instr(dummy, '0')=0 then '~alpabetic' else '~numeric' end from dual

Ответ 15

если условие равно null, то это число

IF(rtrim(P_COD_LEGACY, '0123456789') IS NULL) THEN
                return 1;
          ELSE
                return 0;
          END IF;

Ответ 16

Вы можете использовать этот пример

SELECT NVL((SELECT 1 FROM  DUAL WHERE   REGEXP_LIKE (:VALOR,'^[[:digit:]]+$')),0) FROM DUAL;