By: Daniel Calbimonte | 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 -> 11001110011111**000000**

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

- Take a look at these other SQL Server 2022 related articles.

##### About the author

This author pledges the content of this article is based on professional experience and not AI generated.

**View all my tips**

Article Last Updated: 2023-02-27