在存储逗号分隔值的列值中搜索值 [英] Search a value in the column value that stores comma separated values

问题描述

假设我有一个表 XYZ，其中有一列 Weekend_Days 存储值，SUNDAY,SATURDAY，我有另一个表 ABC，其日期 ACT_DATE 可以是任何日期.现在我必须检查这个日期是否是周末.

Suppose I have table XYZ with a column Weekend_Days which stores the value, SUNDAY,SATURDAY and I have another table ABC with a date, ACT_DATE which can be any date. Now I have to check if the day on this date is a weekend or not.

我尝试使用:

select  ACT_DATE
,       case 
            when UPPER(TO_CHAR(ACT_DATE,'DAY')) IN (SELECT Weekend_Days from XYZ) 
               then 1 
            else 0 
        end as Weekend_Flag 
from ABC

但它不起作用，它只是为所有日期返回 0.

But it is not working, its just returning 0 for all dates.

尝试将 Weekend_Days 的值存储为 ('SUNDAY','SATURDAY') 但没有用.

Tried storing the value of Weekend_Days as ('SUNDAY','SATURDAY') but it didn't work.

样本数据:

表格 XYZ:

WEEKEND_DAYS
---------------
SUNDAY,SATURDAY

表格 ABC:

ACT_DATE
---------
02-Feb-16
06-Feb-16

当前结果:

ACT_DATE  WEEKEND_FLAG
--------- ------------
02-Feb-16            0
06-Feb-16            0

预期结果:

ACT_DATE  WEEKEND_FLAG
--------- ------------
02-Feb-16            0
06-Feb-16            1

推荐答案

Oracle 设置:

CREATE OR REPLACE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN SYS.ODCIVARCHAR2LIST DETERMINISTIC
AS
  p_result       SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

CREATE TABLE xyz ( weekend_days ) AS
SELECT 'SATURDAY,SUNDAY' FROM DUAL;

CREATE TABLE abc ( act_date ) AS
SELECT DATE '2016-02-02' FROM DUAL UNION ALL
SELECT DATE '2016-02-06' FROM DUAL;

查询

SELECT act_date,
       CASE WHEN w.Weekend_day IS NULL THEN 0 ELSE 1 END AS weekend_flag
FROM  abc a
      LEFT OUTER JOIN
      ( SELECT t.column_value AS weekend_day
        FROM   xyz x,
               TABLE( split_String( x.weekend_days ) ) t
      ) w
      ON TRIM( TO_CHAR( a.ACT_DATE, 'DAY' ) ) = w.Weekend_day;

输出:

ACT_DATE  WEEKEND_FLAG
--------- ------------
06-FEB-16            1 
02-FEB-16            0 

替代查询:

SELECT act_date,
       CASE
         WHEN INSTR( x.weekend_days, TRIM( TO_CHAR( act_date, 'DAY' ) ) ) > 0
         THEN 1
         ELSE 0
         END AS weekend_flag
FROM   abc a
       CROSS JOIN
       xyz x;

这将提供相同的输出并且适用于日期名称，但不适用于一般情况，因为您可能会得到与子字符串的误报匹配.

This will give the same output and will work for names of days but will not work for a general case as you might get a false positive match to a sub-string.

这篇关于在存储逗号分隔值的列值中搜索值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！