Find integer value using bitwise logic in SQL

Question

I am migrating old data into a new database structure and I need to find out the total int of 3 BIT fields in SQL.

So for example,

(Col A = 1, Col B = 2, Col C = 4)

Col A, Col B, COL C
 0      0       0   = 0
 1      0       0   = 1 
 0      1       0   = 2
 0      0       1   = 4
 1      1       1   = 7

I have tried in SQL

SELECT Col A & 1, Col B & 2

But not entirely sure if that logic will work..

Thanks in advance

I advise against this. While it seems like a cool trick, it is going to use more space and also not allow indexes on the data. If the goal is to not keep adding bit columns as new true/falses are tracked, I recommend having one field whose value represents the data name and another that represents the value (So, Male and 1, or Mexican and 0, or Human and 1 could be stored within the same two columns) — UnhandledExcepSean
– UnhandledExcepSean, Commented Oct 23, 2014 at 12:49

StuartLC · Accepted Answer · 2014-10-23 12:35:52Z

3

Just multiply the columns by their respective powers of 2. Assuming that ColA is the least significant bit:

SELECT ColA + ColB * 2 + ColC * 4 FROM MyTable;

answered Oct 23, 2014 at 12:35

StuartLC

108k18 gold badges225 silver badges295 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thats simple to some, but not to me, hitting the binary and floating point books. thanks!