Turn varbinary bit ON/OFF

,

The script create two functions one for turning specific bit ON theother for turning it OFF. Also included 3 test / usage samples scripts.

Each functions accept varbinary(max) and bit index as input parameters and return varbinary with requested bit turned ON / OFF.

set nocount on
GO

--------------------------------------------------------------------------------
-- Turn varbinary bit ON
--------------------------------------------------------------------------------
drop FUNCTION [dbo].[fnTurnBitOn] 
GO

create FUNCTION [dbo].[fnTurnBitOn] 
(
    @Buffer        VARBINARY(MAX)
    ,@BitIndex    BIGINT            -- Left-to-right zero based index.
)
RETURNS VARBINARY(MAX)
AS BEGIN
    DECLARE @ByteIndex BIGINT = @BitIndex / 8
    
    IF (@ByteIndex < LEN(@Buffer))
    BEGIN
        DECLARE @Byte VARBINARY(1) = SUBSTRING(@Buffer, @ByteIndex +1, 1)
        DECLARE @Bit TINYINT = 7 -(@BitIndex - @ByteIndex *8)

        SET @Byte = @Byte | POWER( 2, @Bit ) -- Set bit # @BitNo ON 

        SET @Buffer = CAST(STUFF(@Buffer , @ByteIndex+1 , 1 ,@Byte )AS VARBINARY(MAX))
    END

    RETURN @Buffer
END
GO

--------------------------------------------------------------------------------
-- Turn varbinary bit OFF
--------------------------------------------------------------------------------
drop FUNCTION [dbo].[fnTurnBitOff] 
GO

create FUNCTION [dbo].[fnTurnBitOff] 
(
    @Buffer VARBINARY(MAX)
    ,@BitIndex    BIGINT            -- Left-to-right zero based index.
)
RETURNS VARBINARY(MAX)
AS BEGIN
    DECLARE @ByteIndex BIGINT = @BitIndex / 8
    
    IF (@ByteIndex < LEN(@Buffer))
    BEGIN
        DECLARE @Byte VARBINARY(1) = SUBSTRING(@Buffer, @ByteIndex +1, 1)
        DECLARE @Bit TINYINT = 7 -(@BitIndex - @ByteIndex *8)

        SET @Byte = @Byte &~ POWER( 2, @Bit ) -- Set bit # @BitNo OFF 

        SET @Buffer = CAST(STUFF(@Buffer , @ByteIndex+1 , 1 ,@Byte )AS VARBINARY(MAX))
    END

    RETURN @Buffer
END
GO

--------------------------------------------------------------------------------
-- TESTs
--------------------------------------------------------------------------------

declare @buffer varbinary(MAX) = 0x0000000000000000000000
declare @i int = 0
while @i <= LEN(@buffer) * 8
begin

    set @buffer = [dbo].[fnTurnBitOn](@buffer, @i)
    select 'Set Bit #'+ convert(varchar, @i) +' ON : ', @buffer
    set @buffer = [dbo].[fnTurnBitOff](@buffer, @i)
    select 'Set Bit #'+ convert(varchar, @i) +' OFF: ', @buffer
    
    set @i = @i +1
end    
go

--
-- Turn 1350 bytes varbinary bits ON than OFF
--
declare @buffer varbinary(MAX) = 0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
print 'Buffer size: '+ convert(varchar,len(@buffer)) + ' bytes.'
declare @i int = 0
while @i <= LEN(@buffer) * 8
begin

    set @buffer = [dbo].[fnTurnBitOn](@buffer, @i)
    print @buffer
    
    set @i = @i +1
end    

set @i = 0
while @i <= LEN(@buffer) * 8
begin

    set @buffer = [dbo].[fnTurnBitOff](@buffer, @i)
    print @buffer
    
    set @i = @i +1
end    

Rate

4 (5)

Share

Share

Rate

4 (5)