SQL Server Performance Forum – Threads Archive

bitwise

I’ve never done BITWISE operations and I would like some explanations.
I have a 26 bit number as an INT data type "124005" and I would like to convert it to a 24 bit number. How do I go about doing this . ?
I tried the following , which I think gives me the bit in between the 24bit and the 26 bit
DECLARE @number1 INT,@number2 INT
SET @number1 = 124005
SET @number2 = 3
SELECT @number1 & @number2 Thanks in advance ___________________________________
Need an IT job? –http://www.ITjobfeed.com

The & operator looks at each bit of the numbers you’re comparing, and adds up those that are set to 1 in both numbers. In BOL’s example (170 & 75) the only matching bits are at the end: "1010". This is the binary notation of 10, which is the result that you get from the expression. Not sure what you;re trying to do here, though.

The result was use a ^ operator which will give the 24 bit representation from the original 26 bit representation ___________________________________
Need an IT job? –http://www.ITjobfeed.com

Using BITWISE only works with expression integer category data types, bit, binary and varbinary data types. FYI the operator performs logical or , taking each corresponding for both expressions. The bits in the result are set to 1 if only one bits in the input expressions have value one. If both bits are either 0 or 1 the bit in the result will be cleared to the value of 0. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

You can use SUBSTRING on a bitmask to get a subset of bits – check out the CREATE TRIGGER syntax in BOL, where they explain how the COLUMNS_UPDATED() function returns a bitmask, and how to handle that.

Thanks for the replies , the problem I see is that SQL Server doesn’t support a LEFT SHIFT BITWISE OPERATOR . Such as you can find in Javascript , c# and Java i.e << . ___________________________________
Need an IT job? –http://www.ITjobfeed.com

.. then you can help of CLR stored procedures! Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

This is a function I wrote to ‘decode’ a bitwise stored column that I use to drive my backup routines. It’s fairly straightforward and should show an easy example of bitwise op’s. <pre id="code"> CREATE FUNCTION [dbo].[udf_AdminParameter_Options] (@DatabaseName sysname) – SELECT * FROM dbo.udf_AdminParameter_Options (‘Admin’) RETURNS TABLE AS /****************************************************************************** ** **Name: udf_AdminParameter_Options.sql ** **Description: Bitmask configuration value retrieval. ** **Author: G. Rayburn ** **Date: 02/23/2007 ** **Depends on: ** ******************************************************************************* **Modification History ******************************************************************************* ** **Initial Creation: 02/23/2007 G. Rayburn ** ******************************************************************************* ** ******************************************************************************/ RETURN ( SELECT IntValue AS [Bitmask] , IntValue & 1 AS [Opt_Compress] , IntValue & 2 AS [Opt_Delete_First] , IntValue & 4 AS [Opt_Delete_Last] , IntValue & 8 AS [Opt_CHECKSUM] , IntValue & 16 AS [Opt_VERIFYONLY] FROM dbo.[AdminParameter] WHERE DatabaseName = @DatabaseName ); </pre id="code"> Usage: INSERT INTO dbo.[AdminParameter] (DatabaseName, IntValue) VALUES(‘Admin’, (1|2)) IntValue is represented as 3 in the column and subsequently decoded via "SELECT * FROM dbo.udf_AdminParameter_Options(‘Admin’)" in order to represent the operational options set for the backup routine. Clear as mud, no? <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Thanks that did the job ___________________________________
Need an IT job? –http://www.ITjobfeed.com

You’re quite welcome. It was a confusing topic for myself as well, until I spent some time with it and finally learned how.

You know that you can have the same kind of fun with alphanumeric masks? No, not kidding.

True, SQL doesn’t have a bit-shift operatoer per se, but you can use the POWER function to help with bit-wise operations.

]]>