在其他数字集合之间的数字范围内选择观测值

Question

数据集中有一个字符变量，读作'2-6‘或'4-7’。我感兴趣的是选择那些数字3,4,5可能在谱'4-7‘中的观测值，很容易使用下面的语句

where 3 between TO_NUMBER(REGEXP_SUBSTR(MEAS_VALUE,'\d+',1,1)) and TO_NUMBER(substr(REGEXP_SUBSTR(MEAS_VALUE,'(-)\d+',1,1),2)) or
 4 between TO_NUMBER(REGEXP_SUBSTR(MEAS_VALUE,'\d+',1,1)) and TO_NUMBER(substr(REGEXP_SUBSTR(MEAS_VALUE,'(-)\d+',1,1),2)) or 
 5 between TO_NUMBER(REGEXP_SUBSTR(MEAS_VALUE,'\d+',1,1)) and TO_NUMBER(substr(REGEXP_SUBSTR(MEAS_VALUE,'(-)\d+',1,1),2))

我只是想看看是否有一种more..eloquent解决方案，可以将这些范围分组到下面函数测试数据中

蒂娅-

with test (id, MEAS_VALUE) as (
  select 1,'2-5'                   from dual union all --want this
  select 2,'1-2'                from dual union all   --do not want this
  select 3,'5-7'  from dual)   ----want this
  select * from test
  where 3 between TO_NUMBER(REGEXP_SUBSTR(MEAS_VALUE,'\d+',1,1)) and TO_NUMBER(substr(REGEXP_SUBSTR(MEAS_VALUE,'(-)\d+',1,1),2)) 
   or 4 between TO_NUMBER(REGEXP_SUBSTR(MEAS_VALUE,'\d+',1,1)) and TO_NUMBER(substr(REGEXP_SUBSTR(MEAS_VALUE,'(-)\d+',1,1),2)) or 
     5 between TO_NUMBER(REGEXP_SUBSTR(MEAS_VALUE,'\d+',1,1)) and TO_NUMBER(substr(REGEXP_SUBSTR(MEAS_VALUE,'(-)\d+',1,1),2));

Answer 1

使用子查询：

select t.*
from (select t.*,
             TO_NUMBER(REGEXP_SUBSTR(MEAS_VALUE, '\d+', 1, 1)) as lo,
             TO_NUMBER(REGEXP_SUBSTR(MEAS_VALUE, '\d+', 1, 2)) as lhi
      from test t
     ) t
where 3 between lo and hi and
      4 between lo and hi and
      5 between lo and hi;

您可以将条件进一步简化为：

where least(3, 4, 5) >= lo and
      greatest(3, 4, 5) <= hi