SQL Server Performance
  1. Jack Vamvas Member

    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

  2. Adriaan New Member

    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.
  3. Jack Vamvas Member

    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

  4. satya Moderator

    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.
  5. Adriaan New Member

    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.
  6. Jack Vamvas Member

    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

  7. satya Moderator

    .. 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.
  8. Haywood New Member

    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.<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE FUNCTION [dbo].[udf_AdminParameter_Options] (@DatabaseName sysname)<br />-- SELECT * FROM dbo.udf_AdminParameter_Options ('Admin')<br />RETURNS TABLE<br />AS <br />/******************************************************************************<br />**<br />**Name: udf_AdminParameter_Options.sql<br />**<br />**Description: Bitmask configuration value retrieval. <br />**<br />**Author: G. Rayburn<br />**<br />**Date: 02/23/2007<br />**<br />**Depends on: <br />**<br />*******************************************************************************<br />**Modification History<br />*******************************************************************************<br />**<br />**Initial Creation: 02/23/2007 G. Rayburn<br />**<br />*******************************************************************************<br />**<br />******************************************************************************/<br />RETURN<br />(<br />SELECT IntValue AS [Bitmask]<br />, IntValue & 1 AS [Opt_Compress]<br />, IntValue & 2 AS [Opt_Delete_First]<br />, IntValue & 4 AS [Opt_Delete_Last]<br />, IntValue & 8 AS [Opt_CHECKSUM]<br />, IntValue & 16 AS [Opt_VERIFYONLY]<br /> FROM dbo.[AdminParameter] <br />WHERE DatabaseName = @DatabaseName<br />);<br /></font id="code"></pre id="code"><br /><br /><br />Usage:<br /><br />INSERT INTO dbo.[AdminParameter]<br /> (DatabaseName, IntValue)<br /> VALUES('Admin', (1|2)) <br /><br />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.<br /><br />Clear as mud, no? <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />
  9. Jack Vamvas Member

  10. Haywood New Member

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

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

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

Share This Page