Binary Equivalent Of A Number

In the article about determining the bits that are turned on, we saw how to use the SQL Server bitwise operators to play around with the bits of a number. In this article, we will extend this concept a bit further and see how to determine the bit pattern of a given integer number. An integer number occupies 4 bytes of storage, thus you will get a sequence of 0's and 1's as the output which will be the binary equivalent of the number.
 
We will write the program as a function which can then be called to test for various numbers. Here is the logic for the function.

IF (OBJECT_ID('dbo.GetBitMap') IS NOT NULL)
	DROP FUNCTION dbo.GetBitMap
GO
CREATE FUNCTION dbo.GetBitMap (@number INT) RETURNS VARCHAR(50) AS
BEGIN
	DECLARE @bitMask INT
	DECLARE @counter INT
	DECLARE @result VARCHAR(50)

	SET @bitMask = 1
	SET @counter = 1
	SET @result = ''

	WHILE (@counter < 31)
	BEGIN
		IF ((@number & @bitMask) = @bitMask)
			SET @result = @result + '1'
		ELSE
			SET @result = @result + '0'

		SET @bitMask = @bitMask * 2
		SET @counter = @counter + 1
	END
	-- Hits here when the 31st bit needs to be checked. We do it here
	-- since if we multiply by 2, the number we get is 2147483648, which 
	-- results in an overflow
	SET @bitMask = 2147483647
	IF ((@number & @bitMask) = @bitMask)
		SET @result = @result + '1'
	ELSE
		SET @result = @result + '0'

	RETURN (REVERSE (@result))
END
 
The program works by looping through the various bits of the number and seeing if it is turned on/off. The bitwise AND operator is used to test if a bit is turned on or off. We accumulate all the 0's and 1's into a string variable. Since we are starting off with the first bit of the number (number 0) and working towards the last bit, we will need to reverse the string variable and then return the result. Here is a simple program that finds out the binary equivalent of the number 1 to 10 and prints the result.

DECLARE @output TABLE (number INT, bitMap VARCHAR(50))
DECLARE @loopVariable INT
BEGIN
	SET @loopVariable = 1
	WHILE (@loopVariable < 11)
	BEGIN
		INSERT INTO @output (number, bitMap)
			SELECT @loopVariable, dbo.GetBitMap(@loopVariable)
		SET @loopVariable = @loopVariable + 1
	END
	SELECT * FROM @output
END
 
The logic is very simple. We just accumulate the various bit patterns for the number 1 to 10 into a table variable and then print out that table. Here is the output from this program.
 
 
Pretty interesting, right :-) Have fun with programming against bits in SQL Server!!

Home