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

BIT_SET

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

The BIT_SET() function sets the bit value at a given position to a new value:

SELECT
  bit_set(3, 0),
  bit_set(3, 2),
  bit_set(3, 0, 0),
  bit_set(3, 2, 1);
create.select(
  bitSet(inline(3), 0),
  bitSet(inline(3), 2),
  bitSet(inline(3), 0, 0),
  bitSet(inline(3), 2, 1)).fetch();

The result being

+---------+---------+---------+---------+
| bit_set | bit_set | bit_set | bit_set |
+---------+---------+---------+---------+
|       3 |       7 |       2 |       7 |
+---------+---------+---------+---------+

Dialect support

This example using jOOQ:

bitSet(inline(3), 2)

Translates to the following dialect specific expressions:

ASE, SQLDataWarehouse, Sybase

(3 | (1 * CAST(power(2, 2) AS int)))

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

(3 | (1 << 2))

ClickHouse

bitOr(
  3,
  bitShiftLeft(1, 2)
)

DB2, Informix

bitor(
  3,
  (1 * CAST(power(2, 2) AS integer))
)

Exasol

bit_set(3, 2)

Firebird

bin_or(
  3,
  bin_shl(1, 2)
)

H2

bitor(
  3,
  lshift(1, 2)
)

HSQLDB

bitor(
  3,
  (1 * CAST(power(2, 2) AS int))
)

Oracle

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

Snowflake

bitor(
  3,
  bitshiftleft(1, 2)
)

SQLServer

set_bit(3, 2)

Teradata

setbit(3, 2)

Trino

bitwise_or(
  3,
  bitwise_left_shift(1, 2)
)

Access, Derby, Hana

/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL 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