Available in versions: Dev (3.19) | Latest (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)

-- 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.19, 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