oracle - Search a value in the column value that stores comma separated values

Question

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.

I tried using:

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

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

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

Sample Data:

Table XYZ:

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

Table ABC:

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

Current Result:

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

Expected Result:

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

Answer 1

Oracle Setup:

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;

Query

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;

Output:

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

Alternate Query:

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.