搜索表达式中是否包含数字：1-3,5,10-15,20

Question

在oracle数据库表中，我需要查找给定批号的结果。保存批号的字段是一个包含“ 1-3,5,10-15,20”之类的字符串（此字符串内的数字已排序）

有没有办法做到这一点？

在上面的示例中，应该找到以下批号的结果：

1,2,3,5,10,11,12,13,14,15,20

在应用程序中无法做到这一点，因此必须在数据库内部完成。

类似于：“ SELECT * FROM产品的批次= 2”

Answer 1

通过使用REGEXP_SUBSTR函数和分层查询，可以在SQL中完成所有这些操作：

with list_of_ids as (
select regexp_substr(a, '[[:digit:]]+',1, 1) as lot1
     , nvl( regexp_substr(a, '(-)([[:digit:]]+)',1, 1, 'i', '2')
          , regexp_substr(a, '[[:digit:]]+',1, 1)) as lot2
  from (select regexp_substr('1-3,5,10-15,20' , '[^,]+', 1, level) as a
          from dual
       connect by regexp_substr('1-3,5,10-15,20' , '[^,]+', 1, level) is not null
               )
       )
select a.*
  from products a
  join list_of_ids b
    on a.lot between b.lot1 and b.lot2

但是，我必须强调，正确规范数据库是正确的方法。该解决方案可能无法很好地扩展，并且会进行大量不必要的工作。

它是这样的：

首先用逗号分割数据：

SQL>  select regexp_substr('1-3,5,10-15,20', '[^,]+', 1, level) as a
  2     from dual
  3  connect by regexp_substr('1-3,5,10-15,20', '[^,]+', 1, level) is not null
  4          ;

A
--------------
1-3
5
10-15
20

接下来，在最终将其连接到表之前，在连字符上拆分它以提供在BETWEEN中使用的最小和最大手数。NVL可以确保始终保持最大值。

SQL> select regexp_substr(a, '[[:digit:]]+',1, 1) as lot1
  2       , nvl( regexp_substr(a, '(-)([[:digit:]]+)',1, 1, 'i', '2')
  3             , regexp_substr(a, '[[:digit:]]+',1, 1)) as lot2
  4    from (select regexp_substr('1-3,5,10-15,20' , '[^,]+', 1, level) as a
  5            from dual
  6         connect by regexp_substr('1-3,5,10-15,20' , '[^,]+', 1, level) is not null
  7                 )
  8         ;

LOT1           LOT2
-------------- --------------
1              3
5              5
10             15
20             20

SQL>

这是带有完整查询的有效SQL Fiddle。