Bitwise operation with TSQL like rotation, selective bit inversion

Question

How to do bitwise operation with TSQL.

I want to shift 1 bit towards left position. the extreme left bit should become extreme right bit as well.

Eg1:
Declare @a tinyint = 15
--Which is equal to 0000 1111

I need the result to be 30
--Which is equal to 0001 1110

Eg2:
Declare @a tinyint = 16
--Which is equal to 0001 0000

I need the result to be 32
--Which is equal to 0010 0000

Or selectively invert a bit. eg here invert 3rd bit position

input:  0011 0010
result: 0011 1010

For ease of understanding i showed the input in binary, actually the input must be an int type. Like 0001 0000 represents 16.

*"the extreme left bit should become extreme right bit as well."* So you want to rotate bits? — Lukas Eder, Apr 18 '14 at 10:05
@Jithin: it could be an overflow if the very bit is 1, say "1000 0000" — Dmitry Bychenko, Apr 18 '14 at 10:45

Andomar · Accepted Answer · 2014-04-18T10:58:18.977

You can use ^ (exclusive or) to flip a bit. For example:

set @input = 15
set @bit = 4
set @result = @input ^ power(2, @bit - 1)

In a binary system, multiplying by two shifts all the bits one place to the left. So you can implement a 1-bit rotation by a shift and then manually carrying the top bit to the bottom bit:

set @input = 128
set @result = 
    case 
    when @input & 128 > 0 then (@input ^ 128) * 2 + 1
    else @input * 2  
    end

Examples at SQL Fiddle.

Bitwise operation with TSQL like rotation, selective bit inversion

1 Answers1