SQL Functions – Convert Numeric List to Hex Bit Mask

Yesterday we demonstrated how to convert a delimited list into a set using a TSQL function. Once we have the ability to convert a list of items into a set, if the set consists of a set of integers we can convert that list into a binary representation, a Decimal representation (if the list is 64 items or less) and then into a Hex representation.

Consider the following list of items from which an individual may choose:

1 - up

2 - down

3 - in

4 - out

5 - on top

6 - on bottom

7 - on right

8 - on left

9 - after

10 - before

11 - instead

12 - because

13 - off

14 - out

15 - higher

16 - lower

17 - faster

18 - slower

19 - better

20 - best

For our example the user is going to select Up, down, off, out, higher, lower, faster and best. Because there are less than 64 options, all of these selections may be contained in a single unsigned 32 bit integer, having one bit representing one of the above 2o possible selections (with 12 left over). The users selections will turn on the binary bit for the BASE 2 places 1, 2, 13, 14, 15, 16, 17 and 20. Let’s see one way to do this.

Again I am using Common Table Expressions (CTE). I find it easier to read code using CTEs rather than using inline views (nesting select statements), because they are defined in a logic order of usage, and may be referenced more than one time in a following query. The primary reason I am using a CTE is that it replaces looping code.

The first CTE, BinaryList is a simple query providing me with a list of the numbers 1 – 64, with the Integer value that will set the BASE 2 bit to true for that specific number.

Here are the first 8 bits

The second CTE simply returns the list of numbers from my string of selections. It uses the function we created yesterday to convert that delimited string into a list. I convert the string value “Item” into an integer so I can join it to my BinaryList CTE. I also create a bogus field, “IsItem” to be used in a calculation later. I now have a CTE with the values delimited in my string.

My next CTE, ItemBinaryValues, is used to do some final conversion so that I can build my final scalar values. I need the Item column so that I can sort the set, I need a string representation of ‘1’ or ‘0’ so I can build a binary string representation, and I need the Integer value representing the BASE2 place for the selected binary bit position.

In order to build the binary value, including the 0 character when that item has not been selected, the CTE ItemBinaryValues is based on my first CTE that lists all numbers from 1 to 64. I left join the second CTE, List on Item. Now I have a list of numbers from 1 to 64. I also have List.IsItem as 1 or NULL depending on if that Number was selected. Now I can use the fact that Item is 1 or null and turn it into 1 or 0. If that item was selected, I can get the Integer representation by multiplying BinaryList.ItemIntBase2 times List.IsItem. I can also get the one or zero value for that position, used to build the binary string, by selecting ISNULL(List.IsItem, 0).

Now that all the base work is done, I simply concatenate the values from each row into the variables @BitValue which builds the binary representation, and @IntValue which builds the numeric representation. Selecting @BitValue += … and @IntValue += takes the original value and adds the value from each row in my select statement from ItemBinaryValues.

Now, all that is left to do is to convert the BIGINT value of our number into HEX. If you convert a number into VARBINARY(4), it will do the work for you. Selecting the VARBINARY value will give you a human readable HEX representation. Use Varbinary(4) as the data type because that will only hold 64 bits, the largest number allowed in our Unsigned BIGINT.

Many of you have already discovered a hole in my code. It only has 63 bit capability. In other words, you can only store 63 values in my structure. That is because BIGINT in SQL Server is signed. So, the 64th bit is not available. If you try using a different numeric data type it will not automatically convert the Hex for you. Tomorrow I’ll share another little function allowing you to build much larger hex representations. For now you have 63 options.

Cheers,

Ben

DECLARE @Selections VARCHAR(4000) = '1, 2, 13, 14, 15, 16, 17, 20'
DECLARE @BitValue VARCHAR(64) = ''
DECLARE @IntValue BIGINT = 0
DECLARE @HexValue VARBINARY(1)
DECLARE @PowerOf FLOAT = 2

;WITH BinaryList
AS
(
SELECT Number as Item
,CONVERT(BIGINT, POWER(@PowerOf, CONVERT(FLOAT, Number - 1))) As ItemIntBase2
FROM Sequence
WHERE Number BETWEEN 1 and 63
)
,List
AS
(
SELECT CONVERT(INT, Item) AS Item
,1 AS IsItem
FROM dbo.Split(@Selections, ', ')
)
,ItemBinaryValues
AS
(
SELECT
bl.Item
,CONVERT(VARCHAR(1), ISNULL(l.IsItem, 0)) AS BitValue
,bl.ItemIntBase2 * ISNULL(l.IsItem, 0) as ItemIntBase2
FROM BinaryList bl
LEFT JOIN List l on bl.Item = l.Item
)
SELECT @BitValue += BitValue
,@IntValue += ItemIntBase2
FROM ItemBinaryValues
ORDER BY Item DESC

SET @HexValue = CONVERT(VARBINARY(1), @IntValue)

SELECT @BitValue as BinaryValue, @IntValue as DecValue, @HexValue as HexValue