# New functions in SQL Server 2022 - LEFT_SHIFT and RIGHT_SHIFT

By:   |   Updated: 2023-02-27   |   Comments (3)   |   Related: > SQL Server 2022

##### Problem

LEFT_SHIFT and RIGHT_SHIFT are new functions included with SQL Server 2022. In this article, we will learn about these functions and how they can be used.

##### Solution

This article will explain LEFT_SHIFT and RIGHT_SHIFT and provide some examples to understand how they can be used.

## Introduction

As you already know, in the computer world data is stored in a binary format, either as 0s or 1s. 0 is false and 1 is true. The LEFT_SHIFT and the RIGHT_SHIFT functions can be used to manipulate data by shifting bits. These functions are new to SQL Server, but they exist in other database languages like MySQL.

Some of the things you could use these functions for include: encryption and compression of data.

## LEFT_SHIFT Function

The left bit function moves bits to the left. Let's cover the syntax first to understand how it works:

```LEFT_SHIFT(value,shift number)
```

We have two arguments:

• The value or expression is the value we want to move bits. This value can be an integer or binary value.
• Shift number is the number of bits that we want to be left shifted. This value is an integer.

#### LEFT_SHIFT Basic Example

Let's take a look at a basic example:

```SELECT LEFT_SHIFT(14,4)
```

The result of this query is 224.

We used the LEFT_SHIFT function and moved 4 bits to the left.

Let's take a look at how this works.

• Take the number 14 and convert it to binary: 14 -> 1110
• Add four zeros to the right and LEFT_SHIFT the values: 1110 (add 4 zeros) -> 11100000
• Finally, convert the binary value into decimal again: 1110000 > 224.

#### LEFT_SHIFT Example with Expressions

The following example adds 5 zeros to the left of the YEAR 2020.

```SELECT LEFT_SHIFT(YEAR('2020-04-15'),5) VALUE
```

#### Common Error Messages with LEFT_SHIFT

Here is an example where we try to use a string value.

```SELECT LEFT_SHIFT('2223',4) value
```

We get this error: Argument data type varchar is invalid for argument 1 of left_shift function.

This is because a string value was used and the function accepts numeric values.

Here is another example using extra parameters.

```SELECT LEFT_SHIFT(5,4,3) value
```

We get this error: Msg 174, Level 15, State 1, Line 1 The left_shift function requires two argument(s).

The function requires two arguments, but we sent three arguments by mistake.

## RIGHT_SHIFT Function

The other function is the right function, similar to the LEFT_SHIFT except it moves the bits to the right.

```RIGHT_SHIFT(value,shift number)
```

We have two arguments:

• The value or expression is the value we want to move bits. This value can be an integer or binary value
• Shift number is the number of bits we want to be right shifted. This value is an integer.

#### RIGHT_SHIFT Basic Example

Let's look at a basic example:

```SELECT RIGHT_SHIFT(1478,4) value
```

The result of this query is 92.

We used the RIGHT_SHIFT function and moved 4 bits at the right.

Let's take a look at how this works.

• Take the number 1478 and convert it to binary: 1478 -> 10111000110
• Add four zeros to the left and RIGHT_SHIFT the values: 10111000110 (add 4 zeros) -> 00001011100
• Finally, convert the binary value into decimal again: 1011100 > 92.

#### RIGHT_SHIFT Example with Expressions

The following example adds some zeros to the left of the YEAR 2020.

```SELECT RIGHT_SHIFT(YEAR('2020-04-15'),5) VALUE
```

#### Common Error Messages with RIGHT_SHIFT

Here is an example where we try to use a string value.

```SELECT RIGHT_SHIFT('2223',4) value
```

We get this error: Argument data type varchar is invalid for argument 1 of right_shift function.

This is because a string value was used and the function accepts numeric values.

Here is another example using extra parameters.

```SELECT RIGHT_SHIFT(5,4,3) value
```

We get this error: Msg 174, Level 15, State 1, Line 1 The right_shift function requires two argument(s).

The function requires two arguments, but we sent three arguments by mistake.

## Examples of LEFT_SHIFT and RIGHT_SHIFT

Below we will take a look at some examples of LEFT SHIFT and RIGHT SHIFT.

#### Modify Colors

The following example shows how this can be used to change the color value representation.

White in RBG code is rgb(255, 255, 255)

In binary, the code would be: 11111111, 11111111, 11111111

This is because 255 in decimal is 11111111 and we have three values.

Now we will convert the white to black using the RIGHT_SHIFT function.

```SELECT
RIGHT_SHIFT(255,8) val1,
RIGHT_SHIFT(255,8) val2,
RIGHT_SHIFT(255,8) val3
```

The result is: 00000000, 00000000, 00000000

So this would be RGB(0,0,0) which is the color black.

#### Encrypt Data

LEFT_SHIFT and RIGHT_SHIFT are commonly used to hide data.  Let's take a look at an example.

We have a database with some salaries:

``` CREATE TABLE [dbo].[salary](
[id]     [int] NULL,
[user]   [nchar](20) NULL,
[salary] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[salary] ([id], [user], [salary]) VALUES (1, N'dgomez              ', 5000)
INSERT [dbo].[salary] ([id], [user], [salary]) VALUES (2, N'jsmith              ', 5500)
INSERT [dbo].[salary] ([id], [user], [salary]) VALUES (3, N'jmeyers             ', 4567)
GO
```

The data will be something like this:

```select * from dbo.salary
```

However, our boss wants to hide the actual salary, so we could use the LEFT_SHIFT to do this.

`update [dbo].[salary] set [salary] = LEFT_SHIFT(salary,3)`

If we query the salary table, we will get the following values:

```select * from dbo.salary
```

Note, that now it has fake values for the salaries.

Now, we will create a stored procedure to unencrypt the values, here we use RIGHT_SHIFT to undo the change.

```create procedure get_salaries
as
select
Id,
[User],
RIGHT_SHIFT(salary,3) as salary
from
dbo.salary
```

The stored procedure get_salaries can be used to get the real salaries:

```exec dbo.get_salaries
```

Note that these functions are really simple encryptions. It can be used for non-critical data. If you need a more secure way to encrypt data try symmetric keys.

#### Compressing Data

These functions are sometimes used to compress data.

Let's say that we have the following number which is an integer of 4 bytes.

```declare @mynumber int=845760
```

This number in binary has 6 zeros at the right. We could remove those 0s and convert it to a smallint number and compress it to 2 Bytes.

845760 -> 11001110011111000000

The following example compresses the int to a smallint by removing the 6 zeros:

```declare @compressed smallint=(select RIGHT_SHIFT(845760,6))
select @compressed as compressed
```

To unzip the data, we use LEFT_SHIFT:

```select LEFT_SHIFT(13215,6) as uncompressed
```

The result of the query is the integer value again:

##### Next Steps

Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.

View all my tips

Article Last Updated: 2023-02-27