Available in versions: Dev (3.20) | Latest (3.19) | 3.18

BIT_GET

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The BIT_GET() function extracts the bit value at a given position:

SELECT
  bit_get(3, 0),
  bit_get(3, 2);
create.select(
  bitGet(inline(3), 0),
  bitGet(inline(3), 2)).fetch();

The result being

+---------+---------+
| bit_get | bit_get |
+---------+---------+
|       1 |       0 |
+---------+---------+

Dialect support

This example using jOOQ:

bitGet(inline(3), 2)

Translates to the following dialect specific expressions:

ASE, SQLDataWarehouse, Sybase

((3 & (1 * CAST(power(2, 2) AS int))) / CAST(power(2, 2) AS int))

Aurora MySQL, Aurora Postgres, BigQuery, CockroachDB, DuckDB, MariaDB, MemSQL, MySQL, Postgres, Redshift, SQLite, Vertica, YugabyteDB

((3 & (1 << 2)) >> 2)

ClickHouse

bitTest(3, 2)

DB2, Informix

(bitand(
  3,
  (1 * CAST(power(2, 2) AS integer))
) / CAST(power(2, 2) AS integer))

Exasol

CASE bit_check(3, 2)
  WHEN TRUE THEN 1
  WHEN FALSE THEN 0
END

Firebird

bin_shr(
  bin_and(
    3,
    bin_shl(1, 2)
  ),
  2
)

H2

CASE bitget(3, 2)
  WHEN TRUE THEN 1
  WHEN FALSE THEN 0
END

HSQLDB

(bitand(
  3,
  (1 * CAST(power(2, 2) AS int))
) / CAST(power(2, 2) AS int))

Oracle

(bitand(
  3,
  (1 * CAST(power(2, 2) AS number(10)))
) / CAST(power(2, 2) AS number(10)))

Snowflake

bitshiftright(
  bitand(
    3,
    bitshiftleft(1, 2)
  ),
  2
)

SQLServer

get_bit(3, 2)

Teradata

getbit(3, 2)

Trino

bitwise_right_shift(
  bitwise_and(
    3,
    bitwise_left_shift(1, 2)
  ),
  2
)

Access, Derby, Hana

/* UNSUPPORTED */

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo