Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Querying Responses

By Bruce Szabo,

Querying Responses

Querying Responses

Problem:

Having stored our checkbox data as integers how does one query the table to find the corresponding bits which are set?

Background:

Although the pervasiveness of TCP/IP and network masks has made more people aware of the binary number system let’s review how to figure out which bits are set. Given an integer how does one calculate which particular bits are set. A math teacher would give a better explanation on the terminology behind the following process but the following is a process one can use.

The Process:

The following is a list of bits. As I mentioned in the previous article each bit corresponds to a given checkbox.

	Checkbox1      Bit 1     2^0 = 1
	Checkbox2      Bit 2     2^1 = 2
	Checkbox3      Bit 3     2^2 = 4
	Checkbox4      Bit 4     2^3 = 8
	Checkbox5      Bit 5     2^4 = 16
	Checkbox6      Bit 6     2^5 = 32
	Checkbox7      Bit 7     2^6 = 64
	Checkbox8      Bit 8     2^7 = 128

In the last article Storing Responses the process of storing checkbox data as integers was demonstrated. Following the procedures from that article on may end up with an integer value of 37 stored in the database.

Given 37 is a value in the database how would one go backwards to figure out which bits this integer corresponds. The manual process uses the following logic. Find the bit corresponding to a value not larger than 37 but as close to 37 as possible. In this case the value would be 32 which means bit 6 is set. Subtract 37 from the value of the set bit. This will leave 5. Now do the process again. The value closest to 5 without going over is 4, this means bit 3 is set. Take away the 4 from 5 and you are left with 1 this means bit 1 is set. So to represent 37 in binary format we need bits 6, 3 and 1 set.

Programming the above logic can be done. It is a long and tedious process. Another method would be to use the following formula for each of the bits we need to test (Note: we will only need to test the bit greater than the largest integer in the table). Below is the example for each bit followed by the corresponding SQL statement.

 
		Expressions to Return Each Bit
		IntValue: is the integer value
		Mod: is an Access function of the form X MOD Y where returns the remainder when X is divided by Y.
		Transact-SQL has a similar using %
		Int(X): returns the integer portion of the value X 

			Bit1: (Int([intValue]/2^0) Mod 2)
			Bit2: (Int([intValue]/2^1) Mod 2)
			Bit3: (Int([intValue]/2^2) Mod 2)
			Bit4: (Int([intValue]/2^3) Mod 2)
			Bit5: (Int([intValue]/2^4) Mod 2)
			Bit6: (Int([intValue]/2^5) Mod 2)
			Bit7: (Int([intValue]/2^6) Mod 2)
			Bit8: (Int([intValue]/2^7) Mod 2)

Because both Access and SQL can be used to query a SQL database and both are used in my current working environment I have included the SQL statements in using the syntax for each application.

SQL Statement (Access):

		SELECT (Int([intValue]/128) Mod 2) AS Bit8, 
			(Int([intValue]/64) Mod 2) AS Bit7, 
			(Int([intValue]/32) Mod 2) AS Bit6, 
			(Int([intValue]/16) Mod 2) AS Bit5, 
			(Int([intValue]/8) Mod 2) AS Bit4, 
			(Int([intValue]/4) Mod 2) AS Bit3, 
			(Int([intValue]/2) Mod 2) AS Bit2, 
			(Int([intValue]/1) Mod 2) AS Bit1, 
		tblinttest. IntValue as IntegerValue
		FROM tblinttest

To specify a specific bit add a where clause using

		WHERE ((((Int([inttest]/128) Mod 2))=1) AND (((Int([inttest]/64) Mod 2))=1));

SQL Statement (Transact-SQL):

		SELECT (convert(int,([inttest]/128)) % 2) AS Bit8, 
				(convert(int,([inttest]/64)) % 2) AS Bit7, 
				(convert(int,([inttest]/32)) % 2) AS Bit6, 
				(convert(int,([inttest]/16)) % 2) AS Bit5, 
				(convert(int,([inttest]/8)) % 2) AS Bit4, 
				(convert(int,([inttest]/4)) % 2) AS Bit3, 
				(convert(int,([inttest]/2)) % 2) AS Bit2, 
				(convert(int,([inttest]/1)) % 2) AS Bit1, 
		tblinttest.Inttest as IntegerValue
		FROM tblinttest

To specify a specific bit add a where clause using

		WHERE (((convert(int,([inttest]/128)) % 2)=1) AND ((convert(int,([inttest]/64)) % 2)=1));

It is now possible to write a query to return counts and other information from the survey data mentioned in the first article. The next article will discuss performance and other miscellaneous aspects of this table design method.

Total article views: 4422 | Views in the last 30 days: 3
 
Related Articles
FORUM

Converting Integer to Text

Converting Integer to Text

FORUM

convert date object into integer

convert date object into integer

FORUM

Convertion of integer to Datetime format

Convertion of integer to Datetime format

FORUM

How to convert integer to hours,mins and seconds

How to convert integer to hours,mins and seconds

FORUM

convert datatype using derived column transformation

convert string to integer(or any numeric)

Tags
basics    
database design    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones