Geeks With Blogs

Pankaj Sharma Staying Connected In A Disconnected World...

This is my first post, so i am not sure how to proceed. I was very anxious to post something relevant to IT community but due to work loads or a thought that everything is available on net here or there keeping me away to post. But there is always a first time .

Recently when i was working on a change requirement i had to change a table data in a manner in which field data toggles. Essentially it was a production entity table like parts in a batch manufacturing. So the table contained a field [Status] that was of bit type. 1 denoted the true hence active, 0 denoted the false hence inactive state.

I wanted to change the status of parts to inactive which were active and to active which were inactive. There i tried and found the bitwise operator ~ (tilde) in SQL Server that would toggle the bit value. If it is 1 then it would change it to 0 or vice versa.

Lets take an example to demonstrate how it could be used. Lets create a simple table that would hold the details of users who are active or inactive based on their Status.

create table Users(
[Id] int not null IDENTITY,
[Name] varchar(50) not null,
[Status] bit not null
)

INSERT INTO Users VALUES('Setup',1)
INSERT INTO Users VALUES('Operator',0)
INSERT INTO Users VALUES('Engineer',1)
INSERT INTO Users VALUES('Guest',0)

If we fire a select query it would return the following output:

Now if one wants to toggle the status of users means change active users to inactive and inactive users to active, it would require one single query using bitwise operator tilde(~).

Update Users Set [Status] = ~[Status]

Verify it by executing select statement on the table.

Select * from Users

 

Posted on Tuesday, April 5, 2011 3:18 PM | Back to top


Comments on this post: Usage of Bitwise operator in SQL Server

# re: Usage of Bitwise operator in SQL Server
Requesting Gravatar...
Good share Pankaj. Hope to see a lot of tips like this in near future.
Left by Asheesh on Apr 05, 2011 3:30 PM

# re: Usage of Bitwise operator in SQL Server
Requesting Gravatar...
Hey budy nice job..
post more tips
Left by Upendra on Apr 24, 2011 1:35 PM

# re: Usage of Bitwise operator in SQL Server
Requesting Gravatar...
This is very informative post. Its really helpful for beginner as well as developer. Check out this link too Its having a collection of sql operators with wonderful explanation on sql operator.

http://mindstick.com/Articles/06d3a8e1-041c-4b81-8e0a-1de64fb9e2b9/?SQL%20Operator%E2%80%99s

Thanks
Left by Ajay Singh on Dec 23, 2011 3:11 PM

# re: Usage of Bitwise operator in SQL Server
Requesting Gravatar...
Must be very good to work in a company like this one. I wish all employers are like them. Putting fun into business is a unique but effective way to motivate workers. - Nancy Alcorn
Left by Nancy Alcorn on Jan 24, 2012 8:47 AM

Your comment:
 (will show your gravatar)


Copyright © pankajsharma | Powered by: GeeksWithBlogs.net