ใช้ระหว่างตัวดำเนินการสำหรับสตริงที่เก็บตัวเลข

เพียงปล่อยรูปแบบออกจาก 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 "หมายเลขที่ไม่ถูกต้อง" หากคุณมีค่าใด ๆ ที่ไม่ได้แสดงถึงตัวเลข ข้อมูลตัวอย่างของคุณมีค่าที่ดีเท่านั้น แต่คุณบอกว่าสำหรับสนามจริงของคุณ "คาดว่าจะมีข้อมูลประเภทใดก็ได้ เช่น วันที่ ตัวเลข ตัวเลขอัลฟ่า ฯลฯ"

คุณสามารถหลีกเลี่ยงสิ่งนั้นได้ด้วยฟังก์ชันที่พยายามแปลงค่าสตริงที่เก็บไว้เป็นตัวเลข และส่งคืนค่าว่างหากได้รับข้อยกเว้นนั้น ตัวอย่างง่ายๆ:

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

ฉันคิดว่าแนวทางที่ดีที่สุดที่คุณสามารถลองใช้ได้ในเงื่อนไขนี้คือการใช้ฟังก์ชัน TRANSLATE เพื่อกำจัดอักขระตัวอักษรและตัวเลข เมื่อเสร็จแล้ว ตอนนี้เป็นเทคนิคของ OLD school ที่จะตรวจสอบข้อมูลโดยใช้ฟังก์ชัน NOT BETWEEN หวังว่านี่จะช่วยได้

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;