SQLServer -Store Multiple Values in a Single Value

Question(http://www.dbforums.com/showthread.php?t=1201269)
I have a table called SEARCHCRITERIA. It consists of a USERID column and a CRITERIA column. Users will be able to search for other users based on a set of criteria. There are 5 total criteria a user can choose. They can choose as few as none or all five. I'd like to store the criteria chosen as a single number in the SEARCHCRITERIA table. Then use a function to parse out the criteria. For example:

CRITERIAID CRITERIA CRITERIAVALUE
1 AGE 2
2 SEX 4
3 GRADE 8
4 LOCALE 16
5 REGION 32

Answer

--Yes its possible using bitwise operation and (&) operator


eg:
--creating table

create table #c
(
CRITERIAID int,
CRITERIA varchar(100),
CRITERIAVALUE int
)
go
create table #search
(
userid int,
searchSumValue int
)
go
---insert sample records
set nocount on
insert into #c values (1,'Age',2)
insert into #c values (2,'sex',4)
insert into #c values (3,'Grade',8)
insert into #c values (4,'Locale',16)
insert into #c values (5,'Region',32)
go

----sample data in search table

insert into #search select 1,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (2,4,16)
insert into #search select 2,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (4,8,32)
insert into #search select 3,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (2,4,8,32)
insert into #search select 4,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (2,4,8,16,32)
insert into #search select 5,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (2)
insert into #search select 6,sum(CRITERIAVALUE ) from #c where CRITERIAVALUE in (16,32)
go

-----------select multiple values using bitwise (&) operator------

--userid 1 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=1
--userid 2 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=2

--userid 3 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=3
--userid 4 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=4

--userid 5 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=5
--userid 6 from search table
select c.* from #c c ,#search s where c.CRITERIAVALUE=s.searchSumValue&c.CRITERIAVALUE and s.userid=6