使用动态模式匹配数字

Question

我正在尝试编写一个查询，以显示日程表中安排在今天的所有项目。

表中的"day_string“列可以是*(表示每天)、一个数字(EG 5表示仅在每月5号订购)、一系列数字(1、12、24表示仅在每月1号、12号和24号订购)或类似的东西(1,2-5,7-11,19，相当于1,2,3,4,5,7,8,9,10,11,19)。

当该列中有to_char(sysdate，'fmDD')时，我如何连接该表？

Answer 1

您可以使用任何常用技术将逗号分隔的字符串拆分成单独的行；这里使用分层查询：

select id, regexp_substr(day_string, '(.*?)(,|$)', 1, level, null, 1) as days
from your_table
connect by id = prior id
and level <= regexp_count(day_string, ',') + 1
and prior sys_guid() is not null

要获得以下信息：

ID DAYS
-- -----
 1 *
 2 5
 3 1
 3 12
 3 24
 4 1
 4 2-5
 4 7-11
 4 19

然后将范围拆分为from/to值：

with cte1 (id, days) as (
  select id, regexp_substr(day_string, '(.*?)(,|$)', 1, level, null, 1)
  from your_table
  connect by id = prior id
  and level <= regexp_count(day_string, ',') + 1
  and prior sys_guid() is not null
)
select id,
  regexp_substr(days, '(.*?)(-|$)', 1, 1, null, 1) as days_from,
  regexp_substr(days, '(.*?)(-|$)', 1, 2, null, 1) as days_to
from cte;

ID DAYS_FROM DAYS_TO
-- --------- -------
 1 *
 2 5
 3 1
 3 12
 3 24
 4 1
 4 2         5
 4 7         11
 4 19

然后查看当月的当前日期是否与*匹配，或者在from/to值之间的数值上匹配，使用合并填充要匹配的空白tot值：

with cte1 (id, days) as (
  select id, regexp_substr(day_string, '(.*?)(,|$)', 1, level, null, 1)
  from your_table
  connect by id = prior id
  and level <= regexp_count(day_string, ',') + 1
  and prior sys_guid() is not null
),
cte2 (id, days_from, days_to) as (
  select id,
    regexp_substr(days, '(.*?)(-|$)', 1, 1, null, 1),
    regexp_substr(days, '(.*?)(-|$)', 1, 2, null, 1)
  from cte1
)
select *
from cte2
where days_from = '*'
or extract(day from sysdate) between to_number(days_from)
  and to_number(coalesce(days_to, days_from))

这给出了(今天，9号)：

ID DAYS_FROM DAYS_TO
 1 *
 4 7         11

或者使用内联视图而不是CTE：

select *
from (
  select id,
    regexp_substr(days, '(.*?)(-|$)', 1, 1, null, 1) as days_from,
    regexp_substr(days, '(.*?)(-|$)', 1, 2, null, 1) as days_to
  from (
    select id, regexp_substr(day_string, '(.*?)(,|$)', 1, level, null, 1) as days
    from your_table
    connect by id = prior id
    and level <= regexp_count(day_string, ',') + 1
    and prior sys_guid() is not null
  )
)
where days_from = '*'
or extract(day from sysdate) between to_number(days_from)
  and to_number(coalesce(days_to, days_from))

db<>fiddle

我刚刚传递了一个任意的ID值，但是您可以从源表中包含任何想要的数据(只要connect by有一个惟一的字段，如果您使用该方法的话)。

Answer 2

在Alex的回答的帮助下，我最终添加了一个函数：

with 
function number_in_list(num1 in number, string1 in varchar2)
return varchar2 is
begin
if string1 = '*' then return 'true'; end if;
if regexp_like(string1, '[^0-9,\-]') then return 'oops'; end if;
for i in  (SELECT trim(regexp_substr(string1, '[^,]+', 1, LEVEL)) l
     FROM dual
       CONNECT BY LEVEL <= regexp_count(string1, ',')+1
     )
     LOOP
        if i.l is null then return 'false'; end if;
        if i.l like '%-%' then
            if to_number(regexp_substr(i.l, '(.*?)(-|$)', 1, 1, null, 1)) = num1 then
              return 'true';
            end if;
            if to_number(regexp_substr(i.l, '(.*?)(-|$)', 1, 2, null, 1)) = num1 then
              return 'true';
            end if;
            if num1 > to_number(regexp_substr(i.l, '(.*?)(-|$)', 1, 1, null, 1)) and num1 < to_number(regexp_substr(i.l, '(.*?)(-|$)', 1, 2, null, 1)) then
              return 'true';
            end if;
        elsif num1 = i.l then return 'true'; end if;
     END LOOP;
     return 'false';
end;

我可以像这样使用它：

select * from schedule
where number_in_list(to_char(sysdate, 'fmdd'), day_string) = 'true'