Menggunakan operator antar untuk string yang menyimpan angka

Biarkan saja formatnya keluar dari to_number():

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

Format numerik diperlukan untuk konversi ke karakter. Jika Anda meneruskannya ke to_number(), maka ia mengharapkan angka dengan format tersebut -- dan Anda mungkin salah memasukkan jumlah digitnya.

Atau, lebih baik lagi:

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

Atau, lebih baik lagi, ubah kolom agar berisi angka, bukan string.

Sunting:

Jika masalahnya adalah nilai Anda bukan angka (yang sangat berbeda dari pertanyaan awal Anda), maka ujilah. Ini adalah situasi di mana case sesuai dalam klausa where (karena case menjamin urutan evaluasi argumennya:

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'

Jawaban @ GordonLinoff berfungsi dengan data sampel yang Anda tunjukkan, tetapi akan terjadi kesalahan dengan ORA-01722 "nomor tidak valid" jika Anda memiliki nilai apa pun yang tidak mewakili angka. Data sampel Anda hanya memiliki nilai yang baik, tetapi Anda mengatakan bahwa untuk bidang sebenarnya "segala jenis tipe data diharapkan seperti tanggal, angka, alfa numerik, dll."

Anda dapat menyiasatinya dengan fungsi yang mencoba mengonversi nilai string yang disimpan menjadi angka, dan mengembalikan null jika mendapat pengecualian tersebut. Contoh sederhana:

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;
/

Anda kemudian dapat melakukannya

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

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

Apa pun yang tidak dapat dikonversi dan menyebabkan pengecualian kesalahan nilai ORA-06502 akan dianggap nol, yang bukan berada di antara atau tidak di antara nilai apa pun yang Anda berikan.

Jika Anda perlu memeriksa rentang tanggal, Anda dapat melakukan hal serupa, tetapi kemungkinan kesalahan lebih banyak, dan Anda mungkin memiliki tanggal dalam berbagai format; Anda perlu mendeklarasikan pengecualian dan menginisialisasinya ke nomor kesalahan yang diketahuiuntuk menangkap yang Anda harapkan untuk dilihat. Ini belum lengkap, tapi Anda bisa mulai dengan sesuatu seperti:

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';

Meskipun saya biasanya tidak menggunakan between untuk tanggal; jika Anda tidak memilikinya dengan waktu yang ditentukan maka Anda akan lolos begitu saja di sini.

Anda dapat menggunakan when others untuk menangkap pengecualian apa pun tanpa harus mendeklarasikan semuanya, namun bahkan untuk pengecualian ini, hal tersebut berpotensi berbahaya - jika ada sesuatu yang rusak dengan cara yang tidak Anda duga, Anda ingin mengetahuinya, bukan Sembunyikan itu.

Tentu saja, ini adalah pelajaran penting mengapa Anda harus menyimpan data numerik di kolom NUMBER dan tanggal di bidang DATE atau TIMESTAMP - mencoba mengekstrak informasi berguna ketika semuanya disimpan sebagai string adalah hal yang berantakan, menyakitkan, dan tidak efisien.

Saya rasa pendekatan terbaik yang dapat Anda coba dalam kondisi ini adalah menggunakan fungsi TRANSLATE untuk menghilangkan karakter alfanumerik. Setelah selesai semuanya sekarang adalah teknik jadul untuk memeriksa data dengan menggunakan fungsi NOT BETWEEN Semoga ini bisa membantu.

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;