SET_BIT (Transact SQL)
SET_BIT returns expression_value offset by the bit defined by bit_offset. The bit value defaults to 1, or is set by bit_value.
SET_BIT ( expression_value, bit_offset ) SET_BIT ( expression_value, bit_offset, bit_value )
Any integer or binary expression that isn't a large object (LOB).
The same type as expression_value.
The bit_offset parameter is used to identify the nth bit of the data to set. In integer types, the
0th bit is the least significant bit. In binary types, the
0th bit is the least significant bit in the rightmost byte.
bit_value can be an integer or a bit. However, the only valid values for bit_value are 1 and 0, regardless of the data type. SET_BIT will throw an error if bit_value isn't 1 or 0 or null.
SET_BIT will throw an error if bit_offset is negative or greater than the last bit in the data type.
In the initial implementation, Distributed Query functionality for the bit manipulation functions within linked server or ad hoc queries (OPENQUERY) won't be supported.
A. Use SET_BIT to modify a value
In this example, the third bit (at offset 2, zero-based index) is being set to
SELECT SET_BIT ( 0x00, 2 ) as VARBIN1;
The result is
0x04. This is because the expression_value of
0x00 is converted to
0000. SET_BIT changes its third bit (offset 2) to 1, making it
0100. This binary value is then returned as
4 in hexadecimal representation.
B. Use SET_BIT to modify a value with a custom bit_value
In this example, the bit_value is being set to 0 instead of the default of 1.
SELECT SET_BIT ( 0xabcdef, 0, 0 ) as VARBIN2;
The result is
0xABCDEE. The expression_value is converted to binary, which is
1010 1011 1100 1101 1110 1111. SET_BIT changes the first bit to a 0, and the result is returned in hexadecimal format.