====== Roll your own bitmasks ======

SQL's bitwise operators only work for integer datatype ranges -- even if one of your args is (''VAR'')''BINARY'', the other can be no larger than a ''BIGINT''. If you need more than 63 positions in a bitmask, you need to roll your own.

This approach supports the storage of bitmasks ''VARCHAR'' fields, in this implementation up to 255 characters long. Each (non-Unicode) character occupies one byte, and the character's ASCII code serves as a ''TINYINT'' that can be be used as a native bitwise operand.

===== Print bitmask =====
This is just for convenience/confidence! It prints out the bitmask **left to right**. The index of the first bit position is zero.
<code tsql>IF OBJECT_ID('dbo.fn_PrintBitmask') IS NOT NULL
  DROP FUNCTION dbo.fn_PrintBitmask

GO

CREATE FUNCTION dbo.fn_PrintBitmask (
  @str VARCHAR(255)
)
RETURNS VARCHAR(2040)
AS
BEGIN

DECLARE @bitstring VARCHAR(MAX) = @str + ':'

  SELECT @bitstring += ' ' -- space for ease of reading
    + CASE WHEN ASCII(SUBSTRING(@str, N, 1)) &   1 =   1 THEN '1' ELSE '0' END
    + CASE WHEN ASCII(SUBSTRING(@str, N, 1)) &   2 =   2 THEN '1' ELSE '0' END
    + CASE WHEN ASCII(SUBSTRING(@str, N, 1)) &   4 =   4 THEN '1' ELSE '0' END
    + CASE WHEN ASCII(SUBSTRING(@str, N, 1)) &   8 =   8 THEN '1' ELSE '0' END
    + CASE WHEN ASCII(SUBSTRING(@str, N, 1)) &  16 =  16 THEN '1' ELSE '0' END
    + CASE WHEN ASCII(SUBSTRING(@str, N, 1)) &  32 =  32 THEN '1' ELSE '0' END
    + CASE WHEN ASCII(SUBSTRING(@str, N, 1)) &  64 =  64 THEN '1' ELSE '0' END
    + CASE WHEN ASCII(SUBSTRING(@str, N, 1)) & 128 = 128 THEN '1' ELSE '0' END
  --, N
  --, SUBSTRING(@str, N, 1)
  --, ASCII(SUBSTRING(@str, N, 1))
  FROM dbo.Numbers
  WHERE N <= LEN(@str)
  ORDER BY N

  RETURN @bitstring

END
</code>

===== Check if a bit is set =====
<code tsql>IF OBJECT_ID('dbo.fn_IsBitSet') IS NOT NULL
  DROP FUNCTION dbo.fn_IsBitSet

GO

CREATE FUNCTION dbo.fn_IsBitSet (
  @str VARCHAR(255)
, @bitpos INT
) 
RETURNS BIT
AS
BEGIN

  DECLARE @charPos INT = (@bitpos / 8) + 1
  DECLARE @bitVal INT = POWER(2, @bitpos % 8)

  IF ASCII(SUBSTRING(@str, @charPos, 1)) & @bitVal = @bitVal
    RETURN 1
  RETURN 0

END
</code>

===== Flipping bits =====
This function is a generic bit (un)setter. It takes a ''@set'' ''BIT'' parameter and is idempotent.
<code tsql>IF OBJECT_ID('dbo.fn_ChangeBit') IS NOT NULL
  DROP FUNCTION dbo.fn_ChangeBit

GO

CREATE FUNCTION dbo.fn_ChangeBit (
  @str VARCHAR(255)
, @bitpos INT
, @set BIT
) 
RETURNS VARCHAR(255)
AS 
BEGIN

  DECLARE @charPos INT = (@bitpos / 8) + 1
  DECLARE @bitVal INT = POWER(2, @bitpos % 8)

  DECLARE @target INT = @bitVal
  DECLARE @incr INT = @bitVal

  IF @set = 0
  BEGIN
    SET @target = 0
    SET @incr *= -1
  END   

  IF ASCII(SUBSTRING(@str, @charPos, 1)) & @bitVal = @target
    RETURN @str

  RETURN SUBSTRING(@str, 1, @charPos - 1) 
    + CHAR(ASCII(SUBSTRING(@str, @charPos, 1)) + @incr)
    + SUBSTRING(@str, @charPos + 1, LEN(@str))

END</code>

====Set bit ====
This is a wrapper for ''dbo.fn_ChangeBit''.
<code tsql>IF OBJECT_ID('dbo.fn_SetBit') IS NOT NULL
  DROP FUNCTION dbo.fn_SetBit

GO

CREATE FUNCTION dbo.fn_SetBit (
  @str VARCHAR(255)
, @bitpos INT
) 
RETURNS VARCHAR(255)
AS 
BEGIN

  RETURN dbo.fn_ChangeBit(@str, @bitPos, 1)

END</code>

==== Unset bit ====
Like ''dbo.fn_SetBit'', a wrapper for ''dbo.fn_ChangeBit''.
<code tsql>IF OBJECT_ID('dbo.fn_UnsetBit') IS NOT NULL
  DROP FUNCTION dbo.fn_UnsetBit

GO

CREATE FUNCTION dbo.fn_UnsetBit (
  @str VARCHAR(255)
, @bitpos INT
) 
RETURNS VARCHAR(255)
AS 
BEGIN

  RETURN dbo.fn_ChangeBit(@str, @bitPos, 0)

END</code>

{{tag> bitmasks tsql development functions}}