作为初学者：您应该修复数据模型，而不是在单个列中存储多个值。在数据库列中存储值列表基本上违背了关系数据库的目的。下面是关于这个主题的相关阅读。
也就是说，这里有一个使用模式匹配的选项：

case
  when ',' + start_days + ',' like '%,' + datename(weekday,getdate()) + ',%' then 1
  else 0
end as today_flag

如果列表中的值周围确实有单引号，则需要将它们包含在匹配中：

case
  when ',' + start_days + ',' like '%,''' + datename(weekday,getdate()) + ''',%' then 1
  else 0
end as today_flag

如果值始终为工作日，则可以简化此操作，因为不存在值重叠的风险：

case
  when start_days like '%''' + datename(weekday,getdate()) + '''%' then 1
  else 0
end as today_flag

这个问题的正确答案是修正数据模型。像这样存储多个值会导致许多问题，而您现在只能处理一个问题。
在那之前，你可以 LIKE 操作以获得所需的结果：

SELECT *, CASE WHEN 
                 Value LIKE CONCAT('%', QUOTENAME(DATENAME(WEEKDAY,GETDATE()), ''''), '%')
               THEN 1
               ELSE 0
          END
FROM
(
  VALUES
  ('''Monday'',''Tuesday'',''Sunday'''),
  ('''Thursday'''),
  ('''Friday'',''Sunday'''),
  ('''Tuesday'',''Sunday'''),
  ('''Tuesday'',''Wednesday'',''Thursday'',''Friday''')
) T(Value)

这是一个 db<>fiddle 在那里你可以看到它是如何工作的在线。