For the past six months, I’ve been teaching night classes in database programming and my students are just about ready to take Microsoft’s 98-364 exam. Now, we’re transitioning over to C# programming for the next phase of the program but I have to keep their SQL skills fresh for the exam.  Tonight, it was time to teach them to understand the binary number system.  I dropped some hints on it a couple of months back to brace them but now it was time to get it done with once and for all.

Then, as I like to do with this class, I came up with a lesson plan that would incorporate the best of both subjects …

Write stored procedures that will –

• Convert an integer value to a binary string.
• Convert a binary string to an integer.

Since numbers like 10 and 111 can be either binary or decimal, you will need a way for the code calling the stored procedure to indicate which format is intended.

If the procedure is converting to binary, it should always return a multiple of 8 characters. In other words, if it comes up with five, pad it on the left side with three zeros.

The part about padding zeros was just for an extra challenge. I was going to have them do a single procedure that would handle either one but decided that was unrealistic. I wouldn’t write a procedure like that for production so there was no reason to have them do it.

As I was writing up the lesson, I took some time to check Google and make sure there wasn’t some ridiculously simple function within SQL that would do either conversion.  That’s always embarrassing when I code the long way around and then find a single line of code will do it. Besides, one of the students might have found it and it would have blown the lesson.

A quick shout out of thanks to Mark S. Rasmussen whose site supplied a nice, neat algorithm for converting from decimal to binary.  I put my own touches on it for the instructor solution …

CREATE PROCEDURE spDecimalToBinary
@Value BIGINT,
@RetValue VARCHAR(64) OUTPUT
AS

DECLARE @BinaryString VARCHAR(64) = '' -- Working binary string.
DECLARE @BitValue INT -- Used for 1 or 0 of individual bits.

-- Loop until input value is reduced to 0.
WHILE @Value > 0
BEGIN
    -- Get the 1 or 0, add it to the binary string and divide 
    -- the original value in half. The INT type only retains the whole number.
    SET @BitValue = (@Value % 2)  
    SET @BinaryString = CONCAT(@BinaryString, CAST(@BitValue AS VARCHAR(1)))
    SET @Value = @Value / 2    
END

-- Reverse the working string.
SET @BinaryString = REVERSE(@BinaryString)

-- Add zeros to the front of the string until the places are divisible by 8.
WHILE (LEN(@BinaryString) % 8 != 0)
    SET @BinaryString = CONCAT('0', @BinaryString)

-- Return the result.
SET @RetValue = @BinaryString

GO

-- Test

DECLARE @COUNTER INT = 1
DECLARE @BinaryVal VARCHAR(64)

WHILE @COUNTER < 65536
BEGIN
    EXEC spDecimalToBinary @COUNTER, @BinaryVal OUTPUT
    PRINT @BinaryVal
    SET @COUNTER = @COUNTER + 1
END

My style is a little more verbose than Mark’s and heavier on variables but I like to break out the steps and keep working variables separate from return variables.

The test shown above ran in about 5 seconds on my old i3 laptop and spit out all the binary values between 1 and 65535.

My star student finished and tested his solution long before the end of class so I had to come up with extra challenges for him.  First I told him to write the values and results to a table and then I suggested he come up with code to spot and reject invalid characters in the binary input. I was adding that last challenge to my own solution as I suggested it to him.

CREATE PROCEDURE spBinaryToDecimal
@Value VARCHAR(64),
@RetValue BIGINT OUTPUT
AS

DECLARE @BinPlaceVal INT = 1 -- Holds the value of the binary position
DECLARE @BinPlaceNumber INT = LEN(@Value) -- Holds the current binary position
DECLARE @DecValue INT = 0 -- Accumulated decimal value
DECLARE @InvalidChar BIT = 0

-- Move backwards along binary string
WHILE @BinPlaceNumber > 0
BEGIN

    -- Test for invalid characters.
    IF SUBSTRING(@VALUE, @BinPlaceNumber, 1) NOT IN ('1', '0')
    BEGIN
        SET @InvalidChar = 1
        BREAK
    END

    -- Add value of the current binary position to the decimal value.
    SET @DecValue = @DecValue + (CAST(SUBSTRING(@VALUE, @BinPlaceNumber, 1) AS INT) * 
          @BinPlaceVal)

    -- Go to next binary place and incease the value
    SET @BinPlaceNumber = @BinPlaceNumber - 1
    SET @BinPlaceVal = @BinPlaceVal * 2

END

-- If invalid characters were found, return 0.
IF @InvalidChar <> 0 SET @DecValue = 0

-- Return final value.
SET @RetValue = @DecValue

GO

The @BinPlaceVal variable is used to hold the actual value of the binary digit (1, 2, 4, 8, etc..) and add it to the final decimal value.  Each binary character is tested and required to be 1 or 0 or the whole process ends and 0 is returned.

This would actually have been better as a weekend assignment when the students could have taken their time but either way, it kept them focused on using their SQL knowledge.