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)

white image

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

query results

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

black image

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
query results

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
query results

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
query results

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
query results

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:

query results
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Calbimonte 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.

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

Comments For This Article




Monday, March 20, 2023 - 3:19:30 PM - Jeff Moden Back To Top (91028)
Heh... nicely done but I can't even find folks that understand Integer Math, especially when it comes to modulus. This should be a hoot to try to explain. :D

Monday, March 20, 2023 - 10:31:03 AM - Thomas Franz Back To Top (91026)
PS: found an example, where you could use LEFT_SHIFT() / RIGHT_SHIFT() - if you are VERY CPU-bound and need to divide (right) or multiply (left) by 2, you could use those functions instead of the usual division / multiplication. And of course you could increase the second parameter if you want to divide / multiply by 4, 8, 16, 32 ...

But this is all a very special case again and makes your code harder to understand just to maybe save a few milliseconds (to be honest, I don't know, if the compiler is not already smart enough to replace a hard coded division by a power of two by a RIGHT_SHIFT for himself)

Monday, March 20, 2023 - 9:35:46 AM - Thomas Franz Back To Top (91025)
Yes, RIGHT_SHIFT / LEFT_SHIFT works as described, but the examples are not helpful - nobody should "protect" sensitive data by simple obfuscation and we don't really work with color shifting in SQL.

This does not mean, that I could come up with better examples, simply because binary shifting is something that may have been happened in the old assembler times, sometimes in (real) cryptography or image processing, but is outdated in SQL data world today (and / or would be handled by the SQL engine itself internally when we use a table with multiple BIT columns).

The few szenarios, where we may have to work with bits / flags because we have to import data where a bunch of bit columns were delivered as BIGINT, the new GET_BIT() and SET_BIT() functions are usually much more useful than shifting.

PS: I'd guess Microsoft added the shifting functions (now), because it was low effort and used internally already for some stuff or to complete the GET_BIT / SET_BIT function.














get free sql tips
agree to terms