In SQL Server, LEFT_SHIFT() is a bit manipulation function that returns the first argument bit-shifted left by the number of bits specified in the second argument.

The LEFT_SHIFT() function was introduced in SQL Server 2022.

Syntax

The syntax goes like this:

LEFT_SHIFT ( expression_value, shift_amount )

There’s also a << operator that does the same thing. The syntax for this operator goes like this:

expression_value << shift_amount

In both cases, expression_value can be any integer or binary expression that isn’t a large object (LOB).

The shift_amount argument must be an integer type.

Example

Here’s an example to demonstrate:

SELECT LEFT_SHIFT(12345, 1);

Result:

24690

In order to understand the result here, we need to see what happened to the binary representation of that number.

The following table contains the above integer values and their equivalent binary values:

Integer	Binary
12345	0011 0000 0011 1001
24690	0110 0000 0111 0010

If we look at the two binary values, we can see that the second one is almost the same as the first one, except that the bits have been shifted by 1. Basically, a zero has been added to the right, and this has pushed all other bits to the left.

This effect can be easier to see if we remove the spaces:

0011000000111001
0110000001110010

In the top value, the rightmost digit is a 1. In the bottom value, the 1 has been moved one step to the left (and the rightmost digit is now a zero). The same is true for all digits in the bottom value – they have all been moved one step to the left.

The effect can be even easier to see if we do the following:

 0011000000111001
0011000000111001

We’ve effectively just moved the whole value to the left. The LEFT_SHIFT() function does the same thing, but removes the leftmost digit and adds a zero to the right.

The resulting binary value is the equivalent of 24690, and so that’s what is returned by the LEFT_SHIFT() function.

Let’s shift the same value by two:

SELECT LEFT_SHIFT(12345, 2);

Result:

49380

The integer result is quite different again.

Let’s look at the binary representation of the two values:

Integer	Binary
12345	0011 0000 0011 1001
49380	1100 0000 1110 0100

This time the second binary value has had its bits shifted by two.

Negative Shift Amounts

It’s possible to pass a negative value to the LEFT_SHIFT() function in order to shift the bits to the right, instead of to the left:

SELECT LEFT_SHIFT(12345, -1);

Result:

6172

Here’s the binary representation of the two values:

Integer	Binary
12345	0011 0000 0011 1001
6172	0001 1000 0001 1100

So we can see that the bottom value is almost the same as the top value, except that its bits have been shifted to the right by one digit.

This is the same outcome that we’d get if we’d used the RIGHT_SHIFT() function with a shift value of 1.

The Left Shift Operator (<<)

We can alternatively use the left shift operator to do the same thing as the LEFT_SHIFT() function:

SELECT 12345 << 2;

Result:

49380

Here they are together:

SELECT
  LEFT_SHIFT(12345, 2) AS "LEFT_SHIFT",
  12345 << 2 AS "<<";

Result:

LEFT_SHIFT  <<         
----------- -----------
      49380       49380