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.

This is just for convenience/confidence! It prints out the bitmask left to right. The index of the first bit position is zero.

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

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

This function is a generic bit (un)setter. It takes a @set BIT parameter and is idempotent.

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

This is a wrapper for dbo.fn_ChangeBit.

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

Like dbo.fn_SetBit, a wrapper for dbo.fn_ChangeBit.

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