none
Bit Testing in a WHERE Clause

    Question

  • Is there any way in a WHERE clause to test for a particular bit to be on or off in a column of datatype double?

    REvans

    Wednesday, February 27, 2013 6:44 PM

Answers

  • Not really.  floating point numbers are not stored at the bit level in the same way that whole numeric values are.  I'm guessing that you have a much larger issue to address.
    Wednesday, February 27, 2013 8:27 PM
  • SQL Server does not have a datatype double.

    Assuming you mean float or any other type of floating point data type, then should know that a floating point value is not represented (or stored) with one string of bits, but with a value for the exponent and a value for the mantissa.

    So although it is possible to check for a bit in the internal binary representation of a float (for example "select cast(cast(cast(1 as float(53)) as binary(8)) as bigint)&0x0010000000000000"), I am not sure this serves any practical use. So I guess the question is: what do you want to achieve?

    If you want to store bit patterns in a float, then don't. Because the datatype is not suitable for that. Storing a bit pattern requires an exact datatype.


    Gert-Jan

    Wednesday, February 27, 2013 9:25 PM
  • >Is there any way in a WHERE clause to test for a particular bit to be on or off in a column of datatype double?

    You can do Boolean testing on bit,  tinyint, smallint, int & bigint:

    http://www.sqlusa.com/bestpractices/bitdatatype/

    Example for testing the rightmost bit in an INT column:

    -- Bitwise operations
    SELECT	SalesOrderID, 
    		Boolean = IIF(SalesOrderID & POWER (convert(bigint,2), 0) > 0, 'TRUE', 'FALSE')
    FROM AdventureWorks2012.Sales.SalesOrderHeader
    WHERE SalesOrderID & POWER (convert(bigint,2), 0) =1
    ORDER BY SalesOrderID;
    /*
    SalesOrderID	Boolean
    43659	TRUE
    43661	TRUE
    43663	TRUE
    43665	TRUE
    43667	TRUE
    43669	TRUE        */

    Article: SQL Server Bitwise operators store multiple values in one column


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Windows Azure SQL Database Programming & Design




    Wednesday, March 06, 2013 5:38 PM

All replies

  • Not really.  floating point numbers are not stored at the bit level in the same way that whole numeric values are.  I'm guessing that you have a much larger issue to address.
    Wednesday, February 27, 2013 8:27 PM
  • SQL Server does not have a datatype double.

    Assuming you mean float or any other type of floating point data type, then should know that a floating point value is not represented (or stored) with one string of bits, but with a value for the exponent and a value for the mantissa.

    So although it is possible to check for a bit in the internal binary representation of a float (for example "select cast(cast(cast(1 as float(53)) as binary(8)) as bigint)&0x0010000000000000"), I am not sure this serves any practical use. So I guess the question is: what do you want to achieve?

    If you want to store bit patterns in a float, then don't. Because the datatype is not suitable for that. Storing a bit pattern requires an exact datatype.


    Gert-Jan

    Wednesday, February 27, 2013 9:25 PM
  •  "select cast(cast(cast(1 as float(53)) as binary(8)) as bigint)&0x0010000000000000"), "

    I think maybe you have answered my question or at least put me on the right track, but I will have to research it further.  I am using a double datatype in an Access database to store flag values in 4 bit packets.  I don't care about endianness or sign.  After setting bits, the actual value of the field has no particular meaning.  It's just a place to store stuff, and it is working well so far.  I was looking for a way to query the database at the bit level instead of reading each record in an OleDb read loop to see if a particular bit is set.


    REvans

    Wednesday, February 27, 2013 10:21 PM
  • T-SQL is not up to ANSI/ISO Standard SQL; we do not have DOUBLE PRECISION. 

    But more than that, we do not use bit masking and assembly language programming in an abstract high level language. It means that the physical representation is part of the logical data a model. It means that each bit has meaning, so the column is in violationof First Normal Form (1NF) 

    This is like getting a chain saw, not putting gasoline in it and trying to use it like a manual saw to cut trees. What do you want to do? 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, February 28, 2013 12:12 AM
  • If you only need 4 bit packets, then tinyint could easily do this. It is a precise integer data type and consumes only 1 byte storage (per value) instead of 4 or 8 (in case of float).


    Gert-Jan

    Thursday, February 28, 2013 1:04 PM
  • >Is there any way in a WHERE clause to test for a particular bit to be on or off in a column of datatype double?

    You can do Boolean testing on bit,  tinyint, smallint, int & bigint:

    http://www.sqlusa.com/bestpractices/bitdatatype/

    Example for testing the rightmost bit in an INT column:

    -- Bitwise operations
    SELECT	SalesOrderID, 
    		Boolean = IIF(SalesOrderID & POWER (convert(bigint,2), 0) > 0, 'TRUE', 'FALSE')
    FROM AdventureWorks2012.Sales.SalesOrderHeader
    WHERE SalesOrderID & POWER (convert(bigint,2), 0) =1
    ORDER BY SalesOrderID;
    /*
    SalesOrderID	Boolean
    43659	TRUE
    43661	TRUE
    43663	TRUE
    43665	TRUE
    43667	TRUE
    43669	TRUE        */

    Article: SQL Server Bitwise operators store multiple values in one column


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Windows Azure SQL Database Programming & Design




    Wednesday, March 06, 2013 5:38 PM
  • If you only need 4 bit packets, then tinyint could easily do this. It is a precise integer data type and consumes only 1 byte storage (per value) instead of 4 or 8 (in case of float).


    Gert-Jan


    But each tinyint would occupy one column, and the specification that I was given requires one column.

    REvans

    Thursday, March 07, 2013 2:32 PM
  • Moderators, must every reply be marked as the answer to my question!? Do I have some say in whether or not my question was answered?

    REvans

    Thursday, March 07, 2013 3:05 PM
  • Moderators, must every reply be marked as the answer to my question!? Do I have some say in whether or not my question was answered?

    This is my policy only, other moderators may have different policies:

    1. Propose as soon as there is an answer (anyone can do this)

    2. OP can mark/unmark answer(s) any time

    3. If OP does not mark for 7 days (considered abandoned), I mark an answer(s) or recycle the thread

    4. I usually mark all valuable content posts if there is no clear answer & OP did not mark any answer (abandoned thread)


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Windows Azure SQL Database Programming & Design





    Thursday, March 07, 2013 3:21 PM
  • I thought you said you only needed 4 bits. Maybe I misunderstood.

    The int datatype would support up to 32 bits and bigint up to 64 bits. If you need more than 64 bits, then you should rethink your approach.

    Since float consumes 8 bytes (just as many as bigint), there is no way that float would be able to store any more information.


    Gert-Jan

    Thursday, March 07, 2013 8:50 PM
  • You definitely have a right to unmark an answer if it doesn't answer your question. Usually after some time (7 days) moderators attempt to resolve old threads if they don't have an answer marked by OP.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, March 07, 2013 8:54 PM
  • Thank you, Kalman and Naomi, for the clarification.  I can live with that.

    REvans

    Thursday, March 07, 2013 9:30 PM
  • Yep.  I need 64 bits, and that is what I am using successfully so far, except it is a double in an Access database.

    REvans

    Thursday, March 07, 2013 9:32 PM