You could store this as a bitfield and then use boolean logic operators to retrieve the values
for example:
CREATE TABLE [dbo].[testBF](
[field1] [varchar](max) NOT NULL,
[field2] [varchar](max) NOT NULL,
[bitfield] [int] NOT NULL CONSTRAINT [DF_testBF_bitfield] DEFAULT ((0))
) ON [PRIMARY]
Then for selects:
SELECT field1, field2,
CASE WHEN (bitfield & 1) = 1 THEN 'monday ' ELSE '' END +
CASE WHEN (bitfield & 2) = 2 THEN 'tuesday ' ELSE '' END +
CASE WHEN (bitfield & 4) = 4 THEN 'wednesday ' ELSE '' END +
CASE WHEN (bitfield & 8) = 8 THEN 'thursday ' ELSE '' END +
CASE WHEN (bitfield & 16) = 16 THEN 'friday' ELSE '' END as [days of week]
FROM testBF
To find all days that contain tuesday flag (tuesday is the 2nd bit or 2^1 or 2)
SELECT *
FROM aTable
WHERE (bitfield & 2) = 2
or
SELECT *
FROM aTable
WHERE (bitfield & 2) != 0
Note, the template in the second case will work for any bit — that is for friday (the 5th bit or 2^4 or 16) would be
SELECT *
FROM aTable
WHERE (bitfield & 16) != 0
Finally the general case… pass in a number (1 for monday) you get
SELECT *
FROM aTable
WHERE (bitfield & POWER(2,@inNumOfWeekday-1)) != 0
This seems like a lot of work to me, when you could just save it as 5 (or 7 bit fields) but that is how you could do it.
For more examples look at the gist I wrote for another question:
https://gist.github.com/1846338
and the answer: