Use a "case when in" expression where the list values are in a single field?

Question

I'm using SQL Server and would like to check if todays day name is in a list of values in a single field/column.

An example of the column "start_days" contents is:

'Monday','Tuesday','Sunday'
'Thursday'
'Friday','Sunday'
'Tuesday','Sunday'
'Tuesday','Wednesday','Thursday','Friday'

The code I am trying to run on this is:

case
  when datename(weekday,getdate()) in (start_days) then 1
  else 0
end as today_flag

And the result is 0 for every row. Am I doing something wrong here or is it just not possible to use a single field as a list of values in the statement?

A single value cannot contain multiple values. You can't store `'Tuesday','Wednesday','Thursday','Friday'` in a single column in a row, because those are **4** values. `IN` needs to be followed by a list of values, i.e. `{expression} IN ('value1','value2','value3')` or `expression} IN (Column1, Column2, Column3)` or encase a subquery `{expression} IN (SELECT {expression} FROM MyTable)`. *P.S. it's a `CASE` expression. `Case` (`Switch`) statements are not supported in T-SQL.* — Thom A, May 31 '20 at 19:22

score 0 · Accepted Answer · answered May 31 '20 at 19:22

As a starter: you should fix your data model and not store multiple values in a single column. Storing list of values in a database column basically defeats the purpopose of a relational database. Here is a related reading on that topic.

That said, here is one option using pattern matching:

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

If you really have single quotes around values within the list, then we need to include them in the match:

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

If the values always are weekdays, this can be simplified since there is no risk of overlapping values:

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

Thanks for the link and the suggestions that's really useful to know — chipowski, May 31 '20 at 19:35

score 0 · Answer 2 · answered May 31 '20 at 21:20

The right answer to the question is fixing your data modal. Storing multiple values like that will leads you to many issue and you're stuck on one right now.

Until that, you could use LIKE operaor to get the desired results as:

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)

Here is a db<>fiddle where you can see how it's working online.

Use a "case when in" expression where the list values are in a single field?

2 Answers2