SQL WHERE comma delimited int string

I want to in one field on a row a comma-delimited list of ints to represent days of the week.  This will represent days a customer gets service.  For instance, a customer getting service Monday, Wednesday, and Friday would have 2,4,6 stored in the field.  The idea is that I could do something like this:
 
SELECT *
FROM Customer
WHERE ServiceDays IN (2,4,6)

Open in new window

Any ideas on how to do this?
rmariottiAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:

CREATE function [dbo].[values2table]
(
@values varchar(max),
@separator varchar(2)
) returns @res table ([value] varchar(50))
as
begin
declare @value varchar(50)
declare @commapos int, @lastpos int
set @commapos = 0
select @lastpos = @commapos, @commapos = charindex(@separator, @values, @lastpos+1)
while @commapos > @lastpos
begin
	select @value = substring(@values, @lastpos+1, @commapos-@lastpos-1)
	insert into @res select @value where @value <> ''
	select @lastpos = @commapos, @commapos = charindex(@separator, @values, @lastpos+1)
end
select @value = substring(@values, @lastpos+1, len(@values))
insert into @res select @value where @value <> ''
return
end
GO

SELECT *
FROM Customer
WHERE ServiceDays IN (select [value] from values2table('2,4,6', ','))

Open in new window

0
 
Scott PletcherSenior DBACommented:
Use bits as flags rather than separate numbers.

You can use & to check for a bit being on, and | to set the bit on.
0
 
Scott PletcherSenior DBACommented:
For example:

1=Sunday, 2=Monday, 4=Tuesday, 8=Wed, 16=Thu, 32=Fri, 64=Sat

SELECT *
FROM Customer
WHERE (ServiceDays & 2 > 0)
   AND (ServiceDays & 8 > 0)
   AND (ServiceDays & 32 > 0)
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Scott PletcherSenior DBACommented:
Values for all 7 days can be stored in a tinyint (one byte).
0
 
lluddenCommented:
Or you could just normalize your data and put that into a separate table:

CREATE TABLE Customers (CustID int, CustName varchar(50))
CREATE TABLE ServiceDays (CustID int, ServiceDay smallint)

You can have a view or query to denormalize the data if you need it in a different format.
0
 
Scott PletcherSenior DBACommented:
If you want Customers who get service *only* on those three days, and *not* any other days, you could do this:

SELECT *
FROM Customer
WHERE (ServiceDays & 1 = 0)
   AND (ServiceDays & 2 &gt; 0)
   AND (ServiceDays & 4 = 0)
   AND (ServiceDays & 8 &gt; 0)
   AND (ServiceDays & 16 = 0)
   AND (ServiceDays & 32 &gt; 0)
   AND (ServiceDays & 64 = 0)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.