binary bit manipulation - possible?
-
All, I have the following table CREATE TABLE TEST ( TEST1 binary(32) ) go The TEST1 column is a binary with 256 bits. How can I write T-SQL code to turn an arbitrary bit in TEST1 on or off, or check its state? You obviously get arithmetic overflow if you try something like: INSERT INTO TEST VALUES(0) go UPDATE TEST SET TEST1 = TEST1 | POWER(2, 137) /* Try to set bit 138 to "on" */ go "............ Arithmetic overflow occurred ......." Any ideas?
cheers, Neil
-
All, I have the following table CREATE TABLE TEST ( TEST1 binary(32) ) go The TEST1 column is a binary with 256 bits. How can I write T-SQL code to turn an arbitrary bit in TEST1 on or off, or check its state? You obviously get arithmetic overflow if you try something like: INSERT INTO TEST VALUES(0) go UPDATE TEST SET TEST1 = TEST1 | POWER(2, 137) /* Try to set bit 138 to "on" */ go "............ Arithmetic overflow occurred ......." Any ideas?
cheers, Neil
It's really hard. If you want to support yes/no type values in SQL, use a
bitcolumn. The database - well, SQL Server, at least - storesbitcolumns by bit manipulation in a field that's just long enough to store all thebitcolumns in a row. That is, if you have 8bitcolumns in a particular table, they will only take one byte of space per row.Stability. What an interesting concept. -- Chris Maunder
-
All, I have the following table CREATE TABLE TEST ( TEST1 binary(32) ) go The TEST1 column is a binary with 256 bits. How can I write T-SQL code to turn an arbitrary bit in TEST1 on or off, or check its state? You obviously get arithmetic overflow if you try something like: INSERT INTO TEST VALUES(0) go UPDATE TEST SET TEST1 = TEST1 | POWER(2, 137) /* Try to set bit 138 to "on" */ go "............ Arithmetic overflow occurred ......." Any ideas?
cheers, Neil
The int datatype is 4 bytes.
CREATE TABLE BitTesting ( TestField int ) insert into BitTesting (TestField) values (0) UPDATE BitTesting SET TestField = TestField | 138| is the bitwise OR operator.--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
It's really hard. If you want to support yes/no type values in SQL, use a
bitcolumn. The database - well, SQL Server, at least - storesbitcolumns by bit manipulation in a field that's just long enough to store all thebitcolumns in a row. That is, if you have 8bitcolumns in a particular table, they will only take one byte of space per row.Stability. What an interesting concept. -- Chris Maunder
Many thanks for your post. I thought of using the bit type, but then I'd have to have to 256 columns! Not ideal, and it would be hard to correlate them as well. And how would I update column 178 in code without some kind of array? I am looking into using a combination of SUBSTRING(), STUFF(), CAST(), CONVERT() and ASCII() .. I'm sure it's possible with some work! I thought someone must have done this .. don't want to reinvent the wheel..
cheers, Neil
-
The int datatype is 4 bytes.
CREATE TABLE BitTesting ( TestField int ) insert into BitTesting (TestField) values (0) UPDATE BitTesting SET TestField = TestField | 138| is the bitwise OR operator.--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
Many thanks for your post. That idea is fine, but an int has only 4 bytes. That is just 32 bits ... I need a much longer array of bits (256). And I need to be able to set on/off 1 bit at a time... your example of 138 is actually turning on 3 bits: 138 = 128 + 8 + 2 = 10001010 So I am basically running into problems with large numbers....
cheers, Neil
-
Many thanks for your post. That idea is fine, but an int has only 4 bytes. That is just 32 bits ... I need a much longer array of bits (256). And I need to be able to set on/off 1 bit at a time... your example of 138 is actually turning on 3 bits: 138 = 128 + 8 + 2 = 10001010 So I am basically running into problems with large numbers....
cheers, Neil
Oh...guess I didn't read your initial post carefully enough. Maybe you could just have a separate table with the following columns:
FKeyID int BitLoc tinyint BitVal bitThis table could have 256 records for each record in the parent table (BitLoc would be 0 to 255). Just another idea.--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
Oh...guess I didn't read your initial post carefully enough. Maybe you could just have a separate table with the following columns:
FKeyID int BitLoc tinyint BitVal bitThis table could have 256 records for each record in the parent table (BitLoc would be 0 to 255). Just another idea.--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
Thanks for that. That's a neat idea, and one I hadn't thought of. It's more tables to maintain, but it would certainly work. I may just do that .. if I can't find a simple way to hack at a binary(32) directly :-)
cheers, Neil
-
It's really hard. If you want to support yes/no type values in SQL, use a
bitcolumn. The database - well, SQL Server, at least - storesbitcolumns by bit manipulation in a field that's just long enough to store all thebitcolumns in a row. That is, if you have 8bitcolumns in a particular table, they will only take one byte of space per row.Stability. What an interesting concept. -- Chris Maunder
I have worked out how to do this, so I thought I should post the answer here in case anyone is interested: INSERT INTO TEST VALUES(0) /* Turn bit 137 on */ UPDATE TEST SET TEST1 = CONVERT(binary(32), STUFF(TEST1, 137 / 8 + 1, 1, CONVERT(binary(1), SUBSTRING(TEST1, 137 / 8 + 1, 1) | POWER(2, 137 % 8)))) /* Test bit 137 value */ SELECT SUBSTRING(TEST1, 137 / 8 + 1, 1) & POWER(2, 137 % 8) FROM TEST /* Turn bit 137 off */ UPDATE TEST SET TEST1 = CONVERT(binary(32), STUFF(TEST1, 137 / 8 + 1, 1, CONVERT(binary(1), SUBSTRING(TEST1, 137 / 8 + 1, 1) & ~POWER(2, 137 % 8))))
cheers, Neil