Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE department (
  dept_id number(10) NOT NULL, 
  dept_name varchar2(50) NOT NULL, 
  dept_status varchar2(50),
  non_working_days varchar2(150),
  dept_loc varchar2(50)	
);


INSERT INTO department (dept_id,dept_name,dept_status,non_working_days,dept_loc)
  SELECT 1,'ENGNEERING','A','SATURDAY,SUNDAY','US' FROM DUAL UNION ALL
  SELECT 2,'CIVIL','A','SATURDAY','US' FROM DUAL UNION ALL
  SELECT 3,'LAW','S','TUESDAY,FRIDAY','US' FROM DUAL;
3 rows affected

SELECT * 
FROM   department 
WHERE  dept_status = 'A' 
AND    ',' || lower(non_working_days) || ',' NOT LIKE '%,' || 'saturday' || ',%'
AND    dept_loc = 'US';
SELECT * 
FROM   department 
WHERE  dept_status = 'A' 
AND    ',' || lower(non_working_days) || ',' NOT LIKE '%,' || 'monday' || ',%'
AND    dept_loc = 'US';


DEPT_ID
DEPT_NAME
DEPT_STATUS
NON_WORKING_DAYS
DEPT_LOC


1
ENGNEERING
A
SATURDAY,SUNDAY
US

2
CIVIL
A
SATURDAY
US