New functions in SQL Server 2022 - BIT_COUNT, GET_BIT and SET_BIT

By: Daniel Calbimonte   |   Updated: 2023-03-17   |   Comments (5)   |   Related: > SQL Server 2022

Problem

BIT_COUNT, GET_BIT, AND SET_BIT are new functions in the SQL Server 2022 that allow you to check and update binary data. In this article, we will look at these new functions and how they can be used.

Solution

In this article, we will explain these functions and provide examples and typical errors for each to understand how they work.

BIT_COUNT Function

This function returns the number of bits that are set to 1 and the value returned is a bigint (big integer). This function is used where the value is an integer or a binary expression, LOBs (Large Objects) objects are not accepted.

BIT_COUNT(value)

BIT_COUNT Function Example

The following example will show the number of bits set to 1 for the number 10.

SELECT BIT_COUNT(10) as value

The value displayed is 2. This is because 10 in binary is 1010 and contains 2 values that are set to 1.

BIT_COUNT Function Example with Hexadecimal Values

The following example will show the bits set to 1 of the hexadecimal value 1508A:

SELECT BIT_COUNT (0x1508A) as value

The result is 6. This is because 0x1508A in binary is 00010101000010001010 and contains 6 values that are set to 1.

BIT_COUNT Function Error Messages

Example 1:

SELECT BIT_COUNT (1508A) as value;

We get this error: Msg 102, Level 15, State 1, Line 15 Incorrect syntax near ')'.

The problem is that the value is not supported. If you change the value in parenthesis to 0x1508A it will work:

Example 2:

SELECT BIT_COUNT (0x1508A,2) as value;

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

To fix this problem, make sure that just 1 value is used by the function.

Example 3:

SELECT BIT_COUNT ('093') value;

We get this error: Msg 8116, Level 16, State 1, Line 15 Argument data type varchar is invalid for argument 1 of bit_count function.

The problem is that the argument is varchar and it should be a number.

GET_BIT Function

Another function is the GET_BIT function. This function requires two arguments and returns the bit value for the specified position of the binary value. 

GET_BIT Function Example

Let's take a look at an example to understand how it works:

SELECT GET_BIT (10, 2) as value

The value returned by the function is 0. This is because 10 in binary is 1010 and the value in position 2 (0 is the first position from right to left) is a 0.

Here is another example:

SELECT GET_BIT (10, 3) as value

The value returned is 1. This is because 10 in binary is 1010 and the value in position 3 (0 is the first position from right to left) is a 1.

GET_BIT Function with Hexadecimal Values

The following example will show the bit in the third place of a hexadecimal value.

SELECT GET_BIT (0x23aef, 3) as value

The value returned by the function is 1. 0x23aef in binary is 00100011101011101111 and the value in position 3 is a 1 (0 is the first position from right to left).

GET_BIT Function Error Messages

Example 1:

SELECT GET_BIT (23aef, 3) as value

We get this error: Msg 102, Level 15, State 1, Line 15 Incorrect syntax near ')'.

The problem is that the value is not supported. The value should be 0x23aef not 23aef.

Example 2:

SELECT GET_BIT (2, 3, 4) as value

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

This error happens when more than 2 arguments are provided.

Example 3:

SELECT GET_BIT ('23aef', 3) as value

We get this error: Msg 8116, Level 16, State 1, Line 15 Argument data type varchar is invalid for argument 1 of get_bit function.

The problem is that the argument is varchar and it should be an integer or a binary expression.

SET_BIT Function

This function sets a bit value to either 1 or 0 for a specified bit.

SET_BIT Function Example

The following example will set the bit in the first position (right to left, positions start with 0) to 1. Setting the bit to 1 is the default unless specified.

SELECT SET_BIT (14, 0) as value

The value returned by the function is 15. 14 in binary is 1110, so if we set the first value (from right to left) from 0 to 1, the value in binary will be 1111 which is 15 in decimal.

SET_BIT Function with Hexadecimal Values

The following example will set the bit in the third place of a hexadecimal value.

SELECT SET_BIT (0x23aef, 3, 0) as value

The value returned by the query is 0x023AE7. Converting 0x23aef to binary is 00100011101011101111. If we set the 4 value to 0 we get the 00100011101011101111 to this 00100011101011100111.

Finally, the new value is converted to hexadecimal, so 00100011101011100111 is 0x023AE7.

SET_BIT Function Error Messages

Example 1:

You may receive this error if you pass to many parameters:

SELECT SET_BIT (0x23aef, 8, 0, 2) as value

We get this error: Msg 189, Level 15, State 1, Line 24 The set_bit function requires 2 to 3 arguments.

Example 2:

The following error happens when the data type used by the function is invalid:

SELECT SET_BIT ('0x23aef',8,0) as value

We get this error: Msg 8116, Level 16, State 1, Line 15 Argument data type varchar is invalid for argument 1 of bit_count function.

Next Steps

• Check out these related articles:
  • SQL Server 2022 - LEFT_SHIFT and RIGHT_SHIFT
  • SQL Server 2022 Tips

About the author

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