Results 1 to 2 of 2
  1. #1
    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
    3 GRADE 8
    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!

    Thanks in advance!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Jun 2003
    Posts
    269

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


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