UPDATE: 8/20/2009 - This post has been updated several times, so read through for all of the information. I ran into a scenario where I had a numeric field which was composed of flags and I needed to extract them. I was told that you couldn't do bitwise (AND/OR) operations, but some undocumented bitwise system functions existed (like fn_IsBitSetInBitmask). Trying to dig into the system function was not very easy and having no real documentation outside of the "exec sp_helptext 'fn_IsBitSetInBitmask'" query, I wasn't very successful.
I started to have memories from my Digital Logic class where we had to do something similar using modulus with a constrained feedback loop. I pseudo-coded an example function, but found that SQL doesn't support arrays (thinking now, I probably could have used a table variable, but that would have needlessly complicated the situation). Regardless of SQL's deficiencies, using the modulus/feedback method does work, but wasn't going to fit my vision of the solution.
function BitFlags(int val) {
bool[] arr = new bool[8];
int modulus;
for(int i=7; i >= 0; i--) {
int pwr = (2^i);
if (val == pwr) {
arr[i] = true;
} else if (val > pwr ){
modulus = val % (2 ^ i);
if (modulus != 0) {
arr[i] = true;
val = modulus;
}
}
}
return arr;
}
For those who haven't used their math skills in a while, here is an example. My Digital Logic skills are a bit rusty, so I may be incorrect, feel free to correct me if I am wrong. We want to find the bit flags for the number 76; using the modulus/feedback method, we get something like:
// Background
2^7 = 128
2^6 = 64
2^5 = 32
2^4 = 16
2^3 = 8
2^2 = 4
2^1 = 2
2^0 = 1
// Now the fun part, cycle through the range 0-7 for 2^i:
// Calc Output Description
76 % 128 = 0 [0] // Feedback 76
76 % 64 = 12 [1] // Feedback 12
12 % 32 = -- [0] // 12 is smaller than 32, Feedback 12
12 % 16 = -- [0] // 12 is smaller than 16, Feedback 12
12 % 8 = 4 [1] // Feedback 4
4 % 4 = -- [1] // 4 == 4, so the we get a 1 for the output and there is nothing to feedback, so everything past that point is 0
0 % 2 = -- [0] // 0 is smaller than 2, Feedback 0
0 % 2 = -- [0] // 0 is smaller than 2, Feedback 0
Something just did not settle well with me. You just had to be able to do bitwise operations; it seems like elementary funcitonality. I took my C skills and tried to them out in SQL. It threw me for a loop when after evaluating the @val & <number> expression that I would get back the <number> where I was expecting a 1, but 0's everywhere else. I new I was on the write path and was almost there. I did some googling and found:
http://www.sqlusa.com/articles2005/binarypattern/
Which was doing something similar, but didn't exactly fit the situation. It gave me a DUH! moment when I realized that I should just divide by the <number> and POOF, I had the elegant solution I was expecting.
Try it out for your self.
DECLARE @val as tinyint
SET @val = 128+64+16+1
SELECT (@val&128)/128
, (@val&64)/64
, (@val&32)/32
, (@val&16)/16
, (@val&8)/8
, (@val&4)/4
, (@val&2)/2
, (@val&1)/1
--RESULT:
--11010001
UPDATE: My good friend Christopher Lauer used my code and showed me how he used it. I did not find anything like this when I googled and thinking that it would be useful for other people he gave me permission to include it.
USE MSDB;
if not exists (select * from sys.schemas where name = 'my_utility')
exec('create schema my_utility')
go
/*****************************************************************************************
DESCRIPTION: The MSDB.dbo.sysSchedules has a column named freq_interval, this
column may contain a packed bit that needs unpacked to understand what days
the schedule is set to run on. See books on line for information on this
table and column. This function will unpack the bit and return a string
of weekday names that the bit respresents.
Base code provided by Brock Moeller
--SELECT [Monday] = (@bitWise&2)/2, [Tuesday] = (@bitWise&4)/4, [Wednesday]= (@bitWise&8)/8, [Thursday] = (@bitWise&16)/16, [Friday] = (@bitWise&32)/32, [Saturday] = (@bitWise&64)/64, [Sunday] = (@bitWise&128)/128
MAINTENANCE:
[date:name] [description of the change/maintenance being done]
============================== ==================================================
8/19/2009: Christopher M. Lauer Created function.
******************************************************************************************/
CREATE FUNCTION [my_utility].[udf_getDayNameFromBitWise](@bitWise AS INTEGER)
RETURNS Varchar(62) AS
BEGIN
--SET NOCOUNT ON;
/*
DECLARE @bitWise INT;
--SET @bitWise = 63; --Monday, Tuesday, Wednesday, Thursday, Friday
*/
/***** LOCALS ******/
DECLARE @ErrorCode INT, @RowsAffected INT;
SELECT @ErrorCode = 0, @RowsAffected = 0; -- default
/***** END LOCALS ******/
DECLARE @WeekTable TABLE(dName VARCHAR(10), isUsed bit, dNumber tinyint, bitWise int)
INSERT INTO @WeekTable(dName, isUsed, dNumber, bitWise)
SELECT 'Monday', (@bitWise&2)/2, 1, 2
UNION ALL
SELECT 'Tuesday', (@bitWise&4)/4, 2, 4
UNION ALL
SELECT 'Wednesday', (@bitWise&8)/8, 3, 8
UNION ALL
SELECT 'Thursday', (@bitWise&16)/16, 4, 16
UNION ALL
SELECT 'Friday', (@bitWise&32)/32, 5, 32
UNION ALL
SELECT 'Saturday', (@bitWise&64)/64, 6, 64
UNION ALL
SELECT 'Sunday', (@bitWise&128)/128, 7, 128
--SELECT * FROM @WeekTable
DECLARE @ReturnValue as VARCHAR(50)
SELECT @ReturnValue = COALESCE(@ReturnValue, '') + dName + '; '
FROM @WeekTable
WHERE isUsed = 1;
IF(LEN(@ReturnValue) > 1)
begin
SET @ReturnValue = substring(@ReturnValue,1,len(@ReturnValue) -1)
end
RETURN @ReturnValue
END
UPDATE: 8/20/2009 - I had a little free time and discovered an odd occurrence with bitwise operations.
DECLARE @val as tinyint
SET @val = 128+64+16+1
SELECT (@val&10000001)
--RESULT 129
SELECT (@val&01000001)
--RESULT 65
SELECT (@val&00100000) -- @val&32
--RESULT 128 ---- Incorrect! Should be '0'.
It appears that SQLServer 2005 implicitly interprets the number as binary. I can't find any documentation on this issue. The results are accurate for scenarios where what you are looking for matches. However, if you test on something that doesn't match the left value, it doesn't return the correct number. I suppose an answer will have to wait.
On a side note, hexadecimal numbers work as expected like their decimal counterparts.
DECLARE @val as tinyint
SET @val = 128+64+16+1
SELECT (@val&0x40)
--RESULT 64
SELECT (@val&0x20)
--RESULT 0
It still appears that sticking with base 10 is easiest, but if you are good with hex you may find that usefull.
