Handling multiple values in a binary field

[dtempleton]

Is it possible to link each bit in a binary field to a lookup table? Just curious.

Thanks,
Dave

 

[vongrunt]

Do you mean something like this?

create table lookup ( bitval int, value varchar(10) )
insert into lookup values ( 1, 'Bit 0')
insert into lookup values ( 2, 'Bit 1')
insert into lookup values ( 4, 'Bit 2')
insert into lookup values ( 8, 'Bit 3')
insert into lookup values (16, 'Bit 4')

create table blah ( foo varchar (10), bitmask binary(1) )
insert into blah values ( 'Row 1', 27 )
insert into blah values ( 'Row 2',  4 )

select A.foo, B.value
from blah A
inner join lookup B on A.bitmask & B.bitval <> 0

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[dtempleton]

Yes! I'll have to try that out. Thanks.