Categories:

Bitwise Expression Functions

BITSHIFTLEFT

Shifts the bits for a numeric expression n positions to the left.

Aliases:

BIT_SHIFTLEFT

See also:

BITSHIFTRIGHT

Syntax

BITSHIFTLEFT( <expr1> , <n> )
Copy

Arguments

expr1

This expression must evaluate to a data type that can be cast to INTEGER.

n

The number of bits to shift by.

Returns

This returns a signed 128-bit (16-byte) integer, regardless of the size or data type of the input data value.

Usage Notes

  • If the data type of any argument is numeric but not INTEGER (e.g. FLOAT, DECIMAL, etc.), then that argument will be cast to INTEGER.

  • If the data type of any argument is a string (e.g. VARCHAR), then the argument will be cast to INTEGER if possible. For example, the string ‘12.3’ will be cast to 12. If the value cannot be cast to INTEGER, then the value will be treated as NULL.

  • If any argument is NULL, the result is NULL.

  • The result is a signed integer. If the value of the high bit changes (from 0 to 1, or from 1 to 0), the sign of the result is reversed. For example, BITSHIFTLEFT(1, 127) returns a negative number.

  • Bits that are shifted past the end of the 128-bit output value are dropped.

Examples

This example shows how to use BITSHIFTLEFT and BITSHIFTRIGHT:

Create a simple table and data:

CREATE TABLE bits (ID INTEGER, bit1 INTEGER, bit2 INTEGER);
Copy
INSERT INTO bits (ID, bit1, bit2) VALUES 
    (   11,    1,     1),    -- Bits are all the same.
    (   24,    2,     4),    -- Bits are all different.
    (   42,    4,     2),    -- Bits are all different.
    ( 1624,   16,    24),    -- Bits overlap.
    (65504,    0, 65504),    -- Lots of bits (all but the low 6 bits)
    (    0, NULL,  NULL)     -- No bits
    ;
Copy

Execute the query:

SELECT bit1, bit2, BITSHIFTLEFT(bit1, 1), BITSHIFTRIGHT(bit2, 1)
  FROM bits
  ORDER BY bit1;
Copy

Output:

+------+-------+-----------------------+------------------------+
| BIT1 |  BIT2 | BITSHIFTLEFT(BIT1, 1) | BITSHIFTRIGHT(BIT2, 1) |
|------+-------+-----------------------+------------------------|
|    0 | 65504 |                     0 |                  32752 |
|    1 |     1 |                     2 |                      0 |
|    2 |     4 |                     4 |                      2 |
|    4 |     2 |                     8 |                      1 |
|   16 |    24 |                    32 |                     12 |
| NULL |  NULL |                  NULL |                   NULL |
+------+-------+-----------------------+------------------------+
Copy