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))

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 */

(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