Working with bit masks in T-SQL

Decoding bitwise masks can be a bit confusing, so I wanted to share a couple of T-SQL functions I’ve created to make them easier to deal with. If you’d like to read more about bitmasks and applying them in T-SQL, you can read about it at SQL Fool: T-SQL Bitwise Operators.

The first will return the value in a given bit position of an integer – it accepts two parameters (the lookup value and the bit position) and returns a bit for the value in that position. Note that it starts with position zero, so make sure you’re counting correctly:

CREATE FUNCTION dbo.BitwisePosition(
	@value BIGINT,
	@PositionNumber INT
) RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN

	DECLARE @Result BIT
	DECLARE @Mask BIGINT
		SET @Mask = POWER(2,@PositionNumber)

	SET @Result = (CASE @value & @Mask WHEN @Mask then 1 else 0 end)

	RETURN @Result

END
GO
The second function returns a bit (true/false) based on whether a provided bitmask applies to a reference value:
CREATE FUNCTION dbo.BitwiseMask(
	@value BIGINT,
	@Mask BIGINT
) RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN

	DECLARE @Result BIT

	SET @Result = (CASE @value & @Mask WHEN @Mask then 1 else 0 end)

	RETURN @Result

END
GO
Don’t forget to grant them permissions:
GRANT EXECUTE ON dbo.BitwiseMask TO PUBLIC
GRANT EXECUTE ON dbo.BitwisePosition TO PUBLIC
To use these functions, you’d call them as in these examples:
-- Value:   1110001000
-- Position 9876543210
-- Checkpoing position 7, 4, and 0 should return 1, 0, 0
 select dbo.bitwiseposition(904, 7),
		dbo.bitwiseposition(904, 4),
		dbo.bitwiseposition(904, 0)

-- Value:   1110001000 = 904
-- Bitmask: 0010001000 = 136
-- Will return true since mask "fits inside" value
select dbo.bitwisemask(904, 136)

-- Value:   1110001000 = 904
-- Bitmask: 0010001001 = 137
-- false since mask has a bit "outside" value
select dbo.bitwisemask(904, 137)
I hope you find them helpful!