Problem
I need to perform logical operations on my SQL Server database columns. How can I do this in T-SQL?
Solution
T-SQL provides bitwise operators to perform logical operations on SQL Server database table columns. In this tip, we will examine the OR, AND and XOR bitwise operators. We will begin by creating a table with a primary key column, along with two columns of the BIT datatype. Next, we will populate the table with four rows as if we were creating a classic truth table.
Pipe Character (|) is the Bitwise OR Operator in T-SQL
The pipe character (|) is the bitwise OR operator.
The query below produces the truth table for OR operations between the AttributeA and AttributeB columns. The LogicalOR will be 1 if any either AttributeA or AttributeB equals 1.
Ampersand character (&) is the Bitwise AND Operator in T-SQL
The ampersand character (&) is the bitwise AND operator.
The following query produces the truth table for AND operations between the AttributeA and AttributeB columns. The LogicalAND will be 1 if both AttributeA and AttributeB equals 1.
Caret character (^) is the Bitwise Exclusive OR Operator in T-SQL
The caret character (^) is the bitwise XOR (exclusive OR) operator.
The T-SQL code below produces the truth table for XOR operations between the AttributeA and AttributeB columns. The LogicalXOR will be 1 if AttributeA does not equal AttributeB.
Tilde character (~) is the Bitwise NOT Operator in T-SQL
The tilde character (~) is the bitwise NOT operator.
The example below shows the OR operation above along with the negation of the OR operation.
The image below shows the AND operation above along with the negation of the AND operation.
The T-SQL below shows the XOR operation above along with the negation of the XOR operation.
T-SQL code used for this tip
--======================================================
--Create our example table
--======================================================
create table tblLogic
(
pKey integer identity(1,1) not null primary key,
AttributeA bit,
AttributeB bit
)
--======================================================
--Populate to create a truth table
--======================================================
insert into tblLogic values (0,0)
insert into tblLogic values (0,1)
insert into tblLogic values (1,0)
insert into tblLogic values (1,1)
select * from tblLogic
select *, AttributeA|AttributeB as LogicalOR
from tblLogic
select *, AttributeA&AttributeB as LogicalAND
from tblLogic
select *, AttributeA^AttributeB as LogicalXOR
from tblLogic
select *, AttributeA|AttributeB as LogicalOR,
~(AttributeA|AttributeB) as LogicalNOT_OR
from tblLogic
select *, AttributeA&AttributeB as LogicalAND,
~(AttributeA&AttributeB) as LogicalNOT_AND
from tblLogic
select *, AttributeA^AttributeB as LogicalXOR,
~(AttributeA^AttributeB) as LogicalNOT_XOR
from tblLogic
Next Steps
The code above will also work with integer data types. Also, you can chain together the operators to perform logic operations on more than two columns.
Check out these other tips and tutorials:
Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida. He received his Ph.D. in Integrated Computing and M.S. in Instrumental Sciences from the University of Arkansas at Little Rock. He received a B.A. in Physics from Hendrix College. Before joining UWF, he worked as a data warehouse developer for Northrop Grumman Information Systems and prior to that as a database application developer for Acxiom and Euronet. Dr. Snider’s teaching and research interests include data mining, data warehousing, information visualization, and software development.
