September 30, 2011 at 3:08 am
Hi All,
Iam having the following table and values :
create table BitmaskDemo
(ID int Primary key
,Supermarket varchar(10)
,bitmask varbinary(1000)
)
/*
values
1 = Banana 00000001
2 = Strawberries 00000010
4 = Oranges 00000100
8 = Apples 00001000
16 = plums 00010000
32 = pineapple 00100000
*/
Insert into Bitmaskdemo
values (1,'Tesco',7) -- Bananas,strawberries & oranges 00000111
Insert into Bitmaskdemo
values (2,'Sainsburys',31) -- Bananas, strawberries, oranges, apples & plums 00011111
Insert into Bitmaskdemo
values (3,'Morrissons',8) -- Apples 00001000
Insert into Bitmaskdemo
values (4,'Waitrose',24) -- Apples and plums 00011000
Insert into BitmaskDemo
values (5,'Aldi',25) -- Bananas, Apples and Plums 00011001
Insert into BitmaskDemo
values (6,'Somerfield',9) -- Bananas & Apples 00001001
And i want the sql query to get sum of bitmasks as follows :
sum of 4 and 15 is 15 ,
Sum of 1 and 3 is 3 ,
and Sum of 1 and 2 is 3
September 30, 2011 at 3:31 am
Please mention the requirement clearly
thanks
sarat 🙂
Curious about SQL
September 30, 2011 at 3:36 am
HI Sarat,
From the table which i have given as above, I need the query to get the SUM of the bitmask values like :
eg : Select *
from BitmaskDemo
where Bitmask & 17 = 17
like that using SUM function i need select query where i will get for SUM of 4 and 15 as 15 since 4 already exists in 15
September 30, 2011 at 3:56 am
There is no straight way to achive this. You might need to write complex code using BIT operators.
Why do you want to do BIT operations? What is your application requirement. There could be better solutions to your requirements than BIT operations.
September 30, 2011 at 3:58 am
Please provide me the the query that works without using bit operators...
I need it very badly 🙂
September 30, 2011 at 6:32 am
i think your issue is the display of a bitmask.
01011001 is a varchar representation of a bitmask.
you need to do all your math as integers, and display it with a bitmask(maybe);
i don't see any value to the bitmask.
anyway, by inserting integers into your data, you can do exactly what you wanted,a nd even demoed yourself.
CREATE TABLE BitmaskDemo
(ID INT PRIMARY KEY,
Supermarket VARCHAR(10),
SupermarketCart INTEGER
)
--Get a SuperMarket Cart:
--insert into BitmaskDemo(Supermarket,SupermarketCart) SELECT 'Tesco',0 --an empty cart
INSERT INTO BitmaskDemo(ID,Supermarket,SupermarketCart) VALUES (1,'Tesco',7) -- Bananas,strawberries & oranges 00000111
INSERT INTO Bitmaskdemo(ID,Supermarket,SupermarketCart) VALUES (2,'Sainsburys',31) -- Bananas, strawberries, oranges, apples & plums 00011111
INSERT INTO Bitmaskdemo(ID,Supermarket,SupermarketCart) VALUES (3,'Morrissons',8) -- Apples 00001000
INSERT INTO Bitmaskdemo(ID,Supermarket,SupermarketCart )VALUES (4,'Waitrose',24) -- Apples and plums 00011000
INSERT INTO BitmaskDemo(ID,Supermarket,SupermarketCart) VALUES (5,'Aldi',25) -- Bananas, Apples and Plums 00011001
INSERT INTO BitmaskDemo(ID,Supermarket,SupermarketCart) VALUES (6,'Somerfield',9) -- Bananas & Apples 00001001
/*
IDSupermarketSupermarketCart
2Sainsburys31
5Aldi25
*/
Select *
from BitmaskDemo
where SupermarketCart & 17 = 17
;WITH SupermarketData
AS
(
SELECT * FROM BitmaskDemo
),CTE (IntVal, BinVal, FinalBin) AS
(SELECT SupermarketCart IntVal, SupermarketCart % 2 BinVal, CONVERT(varchar(MAX),SupermarketCart % 2) FinalBin FROM SupermarketData
UNION ALL
SELECT IntVal / 2, (IntVal / 2) % 2, CONVERT(varchar(MAX),(IntVal / 2) % 2) + FinalBin FinalBin
FROM CTE
WHERE IntVal / 2 > 0)
SELECT right('000000000' + FinalBin ,7)
FROM CTE
WHERE IntVal =
(SELECT MIN(IntVal)
FROM CTE);
Lowell
September 30, 2011 at 6:49 am
Thanks a lot Lowell, Can you just guide me how you could do SUM the bitmasks...that could be more great help
For eg : SUM of 1 & 2 = 3(1 & 2 are Maskvalues)
September 30, 2011 at 6:53 am
rams.prsk (9/30/2011)
Thanks a lot Lowell, Can you just guide me how you could do SUM the bitmasks...that could be more great helpFor eg : SUM of 1 & 2 = 3(1 & 2 are Maskvalues)
you had already provided the example, this is just a little more long winded of how to get some of the values:
declare @Bananas int
declare @Strawberries int
declare @Oranges int
declare @apples int
declare @plums int
declare @pineapple int
SET @Bananas =1
SET @Strawberries =2
SET @Oranges =4
SET @apples =8
SET @plums =16
SET @pineapple =32
Select *
from BitmaskDemo
where SupermarketCart & (@Bananas + @plums) = (@Bananas + @plums)
Lowell
September 30, 2011 at 7:04 am
thanks Lowell 🙂 you saved me..that is what iam looking for...Iam new to this field..iam learning a lot now...could you please provide me some good example if u have on how to assign mask values and work on them.
September 30, 2011 at 7:14 am
bitmasks are an old school way , from when memory cost dollars per bit, to maximize the amount of data stored in a byte. it had the cost of obfuscating the values from understandability...only someone who knew what the bitmask meant could figure out and use the data.
now , with a terabyte costing under $100 dollars, there's no need to complicate things and make data uncomprehnsible without the secret decoder ring. , so it's more than just frowned upon to use bitmasks in a DBMS system.
bitmasks do not follow one of the cardinal rules of Database Management systems: a column in a row contains *one and only one value* if you need 8 values, you use eight columns.
for educational purposes, there's a value, but i don't think i've seen a valid use of bitmasks in a while now.
You'd be much better off explaining what you are trying to do, so we can show you examples without trying to use bitmask at all.
Lowell
September 30, 2011 at 7:23 am
Hi Lowell,
Thats nice to hear from you ...as told by you it would be great if you could provide me some examples
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply