However, sometimes you need to do some "Rocket Science", or better say "Neurosurgery" when you need to shift value's internal bits.
When I hit that problem I came up with very easy solution with creation of a little function:
USE tempdb;
GO
CREATE FUNCTION dbo.fn_BitShift(
@Num INT
, @Shift SMALLINT /* Positive - Right Shift,
Negative - Left Shift */
, @Circular BIT /* 0 - Not Circular Shift, 1 - Circular Shift
*/
) RETURNS INT AS
BEGIN
DECLARE @BigNum BIGINT = @Num;
WHILE @Shift != 0
SELECT @BigNum = CASE WHEN SIGN(@Shift) > 0
THEN (@BigNum - (@BigNum & 1)) / 2
+ 0x80000000 * (@BigNum & 1) * @Circular
ELSE (@BigNum - (@BigNum & 0x80000000)) * 2
+ SIGN(@BigNum & 0x80000000) * @Circular
END, @Shift -= SIGN(@Shift);
RETURN CAST(SUBSTRING(CAST(@BigNum as BINARY(8)),5,4) as INT);
END
GO
|
How does it work.
At first, here is some theory in the beginning: "Bit shifts" & "Circular shift"Would say we need to shift bits one time in the right direction within our integer number "4".
The binary representation of integer "4" is "00000000 00000000 00000000 00000100".
The Right shift will move digit "1" on one space right and we get following binary number: "00000000 00000000 00000000 00000010", which is integer "2".
Here is an example of doing that via the function:
| /*------------------------ SELECT dbo.fn_BitShift (4, 1, 0); ------------------------*/ ----------- 2 |
The first parameter is "@Num", which is Integer "4".
The second parameter is number of shifts we want to perform. In our case it is "1".
Also, the Sign of the second parameter indicates the shift direction.
Positive values shifting our integer right and negative values shifting it left.
If we want shift our integer "4" two digits left we specify the second parameter as "-2" and will get "00000000 00000000 00000000 00010000", which is number "16".
Here is the example:
| /*------------------------ SELECT dbo.fn_BitShift (4, -2, 0); ------------------------*/ ----------- 16 |
The third variable indicates if we want to do our shift circular. Would say we want to shift our integer "4" three times. with regular shift result will be zero. With circular, the shifted digit will not be lost, but will move in the front and we will get binary number like this: "10000000 00000000 00000000 00000000", which is "-2147483648" in decimals.
Here is the example:
| /*------------------------ SELECT dbo.fn_BitShift (4, 3, 0), dbo.fn_BitShift (4, 3, 1) ------------------------*/ ----------- ----------- 0 -2147483648 |
Because integers are stored within 32 bits and we will try to shift a number 32 times without circulation we will just loose that number. However, with circulation a number will make a whole circle and become itself:
Here is a sample:
| /*------------------------ SELECT dbo.fn_BitShift (8, 32, 1), dbo.fn_BitShift (2, -32, 1); ------------------------*/ ----------- ----------- 8 2 |
Integers "8" & "2" were shifted around in different directions with returning to the same numbers.
If we try to use shift number bigger than 32 it will just continue to shift further. For instance shifting on 33 positions is equivalent to shift on only one position.
Here is a sample:
| SELECT dbo.fn_BitShift (8, 33, 1), dbo.fn_BitShift (8, 1, 1) , dbo.fn_BitShift (2, -33, 1), dbo.fn_BitShift (2, -1, 1); |
Here are my testing samples:
| /*------------------------ SELECT dbo.fn_BitShift (239623632, -32, 1) , dbo.fn_BitShift (239623632, -27, 0) , dbo.fn_BitShift (239623632, -27, 1) , dbo.fn_BitShift (239623632, 32, 1) , dbo.fn_BitShift (239623632, 25, 0) , dbo.fn_BitShift (4, 31, 1) , dbo.fn_BitShift (4, -31, 1) , dbo.fn_BitShift (4, -29, 0); ------------------------*/ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 239623632 -2147483648 -2139995410 239623632 7 8 2 -2147483648 |
The limitation for that shifting function is only 32 bits of an integer number.
No comments:
Post a Comment