# Thread: Store Multiple Values in a Single Value

1. Registered User
Join Date
Feb 2002
Location
Minneapolis, MN
Posts
253

## Unanswered: Store Multiple Values in a Single Value

was hoping someone couild provide some insight into a problem I'm trying to solve.

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
4 LOCALE 16
5 REGION 32

A user performs a search based on AGE, SEX, and LOCALE. I would then store the value 22 (the sum of 2, 4, and 16) in the SEARCH table. I would then need a function to pull out the three individual values.

Has anyone done anything like this before?

If so, any help would be appreciated!

2. Registered User
Join Date
Jun 2003
Posts
269

## Re

--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 (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

-----Hope this will help u
---cheers
------joseph A mallier

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•