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

Question

Я пытаюсь проверить, является ли значение из столбца в запросе 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

Есть идеи как это проверить?

Answer 1

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

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 имеет логический тип данных, SQL не имеет. Таким образом, хотя вы можете объявить функцию, которая возвращает логическое значение, вы не можете использовать такую ​​функцию в запросе SQL.

Answer 2

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

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

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

Answer 3

Ответ Саиша, используя 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/ (но убедитесь, что вы выбрали для этого флажка совпадение в разрывах строк).

Answer 4

Это потенциальный дубликат строк поиска, которые не содержат числовых данных в 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, это может помочь.

Answer 5

Вы можете использовать функцию регулярного выражения '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

Answer 6

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

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

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

Answer 7

Я против использования 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

Answer 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 не будет считаться числом, но вы всегда можете изменить регулярное выражение, чтобы выполнить обратное.

Answer 9

@JustinCave - замена "когда значение" для "когда другие" является хорошим дополнением к вашему подходу выше. Эта небольшая дополнительная настройка, хотя концептуально та же самая, устраняет требование для определения и последующего выделения памяти для вашей переменной 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.

Answer 10

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

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

Тем не менее, вот что вы можете сделать:

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;

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

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

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

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

Answer 11

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

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

Answer 12

Ну, вы можете создать функцию 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;

РЕДАКТИРОВАТЬ: Пожалуйста, отложите до ответа Джастина. Забыл эту маленькую деталь для чистого вызова SQL....

Answer 13

Функция для мобильного номера длиной 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;

Answer 14

Вот простой метод, который:

• не полагается на TRIM
• не полагается на REGEXP
• позволяет указать десятичный разделитель и / или разделитель тысяч (в моем примере "." и ",")
• очень хорошо работает с такими древними версиями Oracle, как 8i (лично протестирован на 8.1.7.4.0; да, вы правильно прочитали)

SELECT
    TEST_TABLE.*,

    CASE WHEN
        TRANSLATE(TEST_TABLE.TEST_COLUMN, 'a.,0123456789', 'a') IS NULL
    THEN 'Y'
    ELSE 'N'
    END
    AS IS_NUMERIC

FROM
    (
    -- DUMMY TEST TABLE
        (SELECT '1' AS TEST_COLUMN FROM DUAL) UNION
        (SELECT '1,000.00' AS TEST_COLUMN FROM DUAL) UNION
        (SELECT 'xyz1' AS TEST_COLUMN FROM DUAL) UNION
        (SELECT 'xyz 123' AS TEST_COLUMN FROM DUAL) UNION
        (SELECT '.,' AS TEST_COLUMN FROM DUAL)
    ) TEST_TABLE

Результат:

TEST_COLUMN IS_NUMERIC
----------- ----------
.,          Y
1           Y
1,000.00    Y
xyz 123     N
xyz1        N

5 rows selected.

Конечно, это, возможно, не самый мощный метод из всех; например, "." ошибочно идентифицируется как числовое. Однако это довольно просто и быстро, и он может очень хорошо выполнять свою работу, в зависимости от фактических значений данных, которые необходимо обработать.

Для целых чисел мы можем упростить операцию Translate следующим образом:

TRANSLATE(TEST_TABLE.TEST_COLUMN, 'a0123456789', 'a') IS NULL

Как это устроено

Из вышесказанного обратите внимание на Translate синтаксис функции TRANSLATE(string, from_string, to_string). ТеперьTranslate функция не может принять NULL как to_stringаргумент. Итак, указав'a0123456789' как from_string а также 'a' как to_string, происходят две вещи:

• характер a остался один;
• числа 0 к 9 не заменяются ничем, поскольку замена для них не указана в to_string.

Фактически числа отбрасываются. Если результат этой операцииNULL это означает, что для начала это были чисто цифры.

Answer 15

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

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

Answer 16

Обратите внимание, что регулярное выражение или подходы к функциям в несколько раз медленнее, чем простое условие 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

Answer 17

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

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

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

1. Проверяемое числовое подмножество может быть изменено по желанию путем соответствующего изменения регулярного выражения.

2. Это может быть использовано в SQL, т.е. выберите isInteger(myCol) из mytable; так как он возвращает "T" из "F" вместо логического.

3. Он может быть использован изначально в pl/sql т.е. если isInteger(vMyValue) = 'T', тогда....

4. Он удовлетворяет утверждению чистоты WNDS,WNPS.

5. На мой взгляд, он не опирается на слишком широкий спектр "когда другие" подходят для определения результата.