Sql Server: Convert base2 string into integer

convert-base2-to-int.sql

-- This sql snippet converts a base2 string (0 and 1 bits)
-- into an integer value

DECLARE @bitmask VARCHAR(8) = '10000000'
--              Bitposition:   76543210

DECLARE @intValue INT

;WITH bits(pos) AS (
 select  1 union select  2 union select  3 union select  4 union
 select  5 union select  6 union select  7 union select  8 union
 select  9 union select 10 union select 11 union select 12 union
 select 13 union select 14 union select 15 union select 16
)
SELECT @intValue = SUM( SUBSTRING(@bitmask, pos, 1) * POWER(2, LEN(@bitmask) - pos) )
  FROM bits
  
SELECT @bitmask, @intValue, CAST(@intValue as VARBINARY(10))