Working with a Binary Value as a Bitmap

I asked this question earlier and accepted an answer that proposed a CLR function as a solution. However that solution turns out to be unacceptable, as our function will be delpoyed on several client servers offsite and we are not able to guarentee the security settings will allow us to run a CLR function.

The database has a VARBINARY(288) column (lets call it Availability). This column represents the availability of a time period within a span of time. Each byte represents a five-minute interval, and the length of the value is reflected by the start and end time of the time span. Thus, each one-hour span is represented by 12 bytes. When the record is initially created, the Availability column is populated with all ones. Thus, for a two hour time span, the value will initially be 0x111111111111111111111111.

When a time period is taken, the corresponding bytes are changed to 0. The high-order byte represents the start time of the time span, thus, in the above example, if the two hour slot was from 8 AM to 10 AM, the first byte represents 8 AM, the second 8:05, the third 8:10 and so on. Thus, if a 15-minute appointment to be shown from 8:30 to 8:45 in the above example, the value would be changed to 0x111111000111111111111111.

The application is an appointment system. My role is data conversion to get the client's existing data into our database. Once in, going forward, the application will handle the settings, but for data conversion, I have to make sure everything is set properly in the database as I am doing conversion.  As a result, I need three functions to accomplish various tasks:
1) Passing a 'base' start time, an appointment start and end time, and the VARBINARY value, I need a function to return either true or false if the requested time slot is available (all bytes are 1s).
2) Passing the same parameters, I need to return an updated VARBINARY value that represents the value after marking a slot as taken (setting the appropriate bytes to 0)
3) Passing the same parameters, I need to return an updated VARBINARY value that represents the value after marking a slot as available (setting the appropriate bytes to 1), which would happen if I convert a record that shows an appointment, but a subsequent record indicates the appointment was cancelled or rescheduled.

Using the same example above, if I wanted to set an appointment from 8:30 to 8:45 in a slot that represented from 8 AM to 10 AM, I would pass 8:00 AM, 8:30 AM, 8:45 AM and the VARBINARY value.

Assistance GREATLY appreciated.
LVL 17
Doug BishopDatabase DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Well, you can use Substring function against varbinary to get 1, 2, 4 or 8 bytes, cast it to one of integer types and do bitmap operations against them. Something like below.

As the side note, I'd probably change the design and use relation structures rather than bitmasks. It would be simpler in the long run (think about reporting)

	@A varbinary(128) = 0x0123456789ABCDEF
	,@B int
select SUBSTRING(@A,1,4)
select @B = CONVERT(int,SUBSTRING(@A,1,4))
select @B & 0xFF000000

Open in new window

Doug BishopDatabase DeveloperAuthor Commented:

Sorry, the structure cannot be changed. The application design is set and the database structure was built around it (using entity framework to design the database) which suck! I've never worked with such a kludge!!

I'll try to play with your suggestion on Monday.

btw. No byte will ever have a value other than 0 or 1.
Doug BishopDatabase DeveloperAuthor Commented:
Okay, to start, I wrote the following code:

SELECT      StartTime ,      -- time(0)
                  EndTime ,            -- time(0)
                  AvailabilityBits,      -- varbinary(288)
                  CAST(SUBSTRING(AvailabilityBits, 10, 1) AS TINYINT) & 0x1
FROM      dbo.AvailabilityBits

This was the results:
10:00:00      12:00:00      0x111111111111111111111111      1
11:00:00      13:00:00      0x000000000000111111111111      1
06:00:00      20:00:00      0x000000000000000000000000      0

Only the first row shold have had a value of 1, as the string of 1's starts in position 13 on the second.

When I changed the starting position to 13, all three rows returned a value of 0 when I ANDed the byte with 0x1, although the first two should have been 1s.

Further debugging shows that the VARBINARY strings are only 12-bytes long (using LEN() function) although the valuse are 24 bytes. What am I missing?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

This is hexadecimal representation. Each 1/0 value represents 4 bits only. In the second row you have 0s in the first 6 bytes and 0x11 in the last 6 bytes.
Doug BishopDatabase DeveloperAuthor Commented:
Too much down time with the holidays. :-)

So how do I do the comparison a byte at a time? Each byte is to represent 5 minutes and most time periods that I will want to check are for 15-minutes. I want to initially set my varbinary column to all 1s. Which menas if I want to represent 2-hours, I need a string of 24 bytes, each with a value of 1 (5-minutes * 12 slots/hour * 2 hours), and then check, set or clear bytes accordingly.

It may be simpler than my mind is trying to make it but right now I'm juggling 10 things and this is just a small flame on a side-burner.

Doug BishopDatabase DeveloperAuthor Commented:
Actually, how do I check 4-bits at a time?
Checking 4 bits - see the code

As for 24 bytes - you'll need to do it either on byte-by-byte level or, maybe with 3 bigints. Just get the substring of corresponding size, cast it to one of the data types that support bitmask operators (1 byte - tinyint, 2 bytes - smallint, 4 bytes - int, 8 bytes - bigint) and apply bitmask operators.

To change/set the bytes - you can use STUFF function.

Sorry - there is no easy ways :(

	@V varbinary(288) =  0x111111111111111111111111
	,@T tinyint
select @T = SUBSTRING(@V,1,1)
select @T
	, CASE WHEN @T & 0xF0 <> 0 THEN 'High 4 bits are not empty' ELSE 'High 4 bits are empty' END as [High bits]
	, CASE WHEN @T & 0x0F <> 0 THEN 'Low 4 bits are not empty' ELSE 'Low 4 bits are empty' END as [Low bits]

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Doug BishopDatabase DeveloperAuthor Commented:
Actually, it is a true BYTE map (8 bits). Each byte can be either 0 or 1. I was able to get my functions to work properly. I think I just needed a "push" in the right direction. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.