Использование между оператором для строки, которая хранит числа

Просто оставьте формат вне to_number():

select * 
from test  
where to_number(val) not between to_number('0') and to_number('128');

Числовой формат необходим для преобразования в символ. Если вы передадите его в to_number(), то он ожидает число в этом формате, и вы можете ошибиться с количеством цифр.

Или, еще лучше:

select * 
from test  
where to_number(val) not between 0 and 128;

Или, что еще лучше, измените столбец, чтобы он содержал число, а не строку.

РЕДАКТИРОВАТЬ:

Если проблема в том, что ваше значение не является числом (что сильно отличается от исходного вопроса), проверьте это. Это одна из ситуаций, когда case уместно в предложении where (поскольку case гарантирует порядок оценки своих аргументов:

where (case when regexp_like(val, '[^-0-9]') then 'bad'
            when cast(val as number) < 0 then 'bad'
            when cast(val as number) > 128 then 'bad'
            else 'good'
       end) = 'bad'

Ответ @GordonLinoff работает с показанными вами примерами данных, но будет ошибка с ORA-01722 «недопустимый номер», если у вас есть какие-либо значения, которые не представляют числа. В ваших примерах данных есть только хорошие значения, но вы сказали, что для вашего реального поля «ожидается любой тип данных, например дата, числа, буквенно-цифровые и т. Д.».

Вы можете обойти это с помощью функции, которая пытается преобразовать сохраненное строковое значение в число и возвращает null, если получает это исключение. Простой пример:

create function safe_to_number (p_str varchar2) return number is
begin
  return to_number(p_str);
exception
  when value_error then
    return null;
end;
/

Затем вы можете сделать

select * 
from test  
where safe_to_number(val) not between 0 and 128;

VAL      
----------
129       
200       

Все, что не может быть преобразовано и вызывает исключение ошибки значения ORA-06502, будет рассматриваться как нуль, который не находится ни между, ни не между любыми указанными вами значениями.

Если вам нужно проверить диапазоны дат, вы можете сделать что-то подобное, но возможны другие ошибки, и у вас могут быть даты в нескольких форматах; вам нужно будет объявить исключения и инициализировать их известными номерами ошибок чтобы поймать те, которые вы ожидаете увидеть. Это не завершено, но вы можете начать с чего-то вроде:

create function safe_to_date (p_str varchar2) return date is
  l_formats sys.odcivarchar2list;
  format_ex_1 exception;
  format_ex_2 exception;
  format_ex_3 exception;
  format_ex_4 exception;
  format_ex_5 exception;
  pragma exception_init(format_ex_1, -1840);
  pragma exception_init(format_ex_2, -1841);
  pragma exception_init(format_ex_3, -1847);
  pragma exception_init(format_ex_4, -1858);
  pragma exception_init(format_ex_5, -1861);
  -- add any others you might get
begin
  -- define all expected formats
  l_formats := sys.odcivarchar2list('YYYY-MM-DD', 'DD/MM/YYYY', 'DD-MON-RRRR'); -- add others
  for i in 1..l_formats.count loop
      begin
        return to_date(p_str, l_formats(i));
      exception
        when format_ex_1 or format_ex_2 or format_ex_3 or format_ex_4 or format_ex_5 then
          -- ignore the exception; carry on and try the next format
          null;
      end;
  end loop;

  -- did not match any expected formats
  return null;
end;
/

select * 
from test  
where safe_to_date(val) not between date '2016-02-01' and date '2016-02-29';

Я думаю, что лучший подход, который вы можете попробовать в этом состоянии, — использовать функцию ПЕРЕВОД, чтобы исключить буквенно-цифровые символы. После того, как это будет сделано, все теперь будет СТАРОЙ школьной техникой для проверки данных с помощью функции НЕ МЕЖДУ Надеюсь, это поможет.

SELECT B.NM
FROM
  (SELECT a.nm
  FROM
    (SELECT '0' AS nm FROM dual
    UNION
    SELECT '1' AS nm FROM dual
    UNION
    SELECT '68' AS nm FROM dual
    UNION
    SELECT '129' AS nm FROM dual
    UNION
    SELECT '200' AS nm FROM dual
    UNION
    SELECT '125a' AS nm FROM dual
    )a
  WHERE TRANSLATE(a.nm, ' +-.0123456789', ' ') IS NULL
  )b
WHERE b.nm NOT BETWEEN 1 AND 128;