The comprehensive SQL bitwise operations compatibility list

One of the rather nasty, off-the-beaten-track battlefields in SQL non-standards are bitwise operations. To my knowledge, they’re not in any SQL standard (I’ve checked SQL:2008 drafts), but almost all databases support some form of bitwise operations. Here’s the relevant MySQL documentation page, as an example: So we’re talking about these operations: bit_count(), and (&), or (|), xor (^), not (~), left shift (<<), right shift (>>). Agreed, that’s not the most important functionality in a database. But these operations may come in handy every now and then.

The Ranking

So here’s the ranking of bitwise support as far as the upcoming jOOQ support is concerned:

1. Making the world a better place

These databases are bit-friends. This is probably because some C / Assembler guys are on the development teams. I don’t want to have a look at the source code… The winners are:
  • MySQL (Max score! The only one to support bit_count())
  • Postgres

2. Attending their duty

Nothing wrong with these databases. They support the important operations: and (&), or (|), xor (^), not (~). The left / right shift isn’t so important anyway…
  • DB2 (Even has support for BITANDNOT! Operations are called BITAND, BITOR, BITXOR, BITNOT)
  • SQLite (Misses out on the xor (^) operator, though)
  • SQL Server
  • Sybase Adaptive Server
  • Sybase SQL Anywhere

3. Wait a minute

Where did the not (~) operation go on these ones? Hmm… But we still have and (&), or (|), xor (^)
  • H2 (Operations are called BITAND, BITOR, BITXOR)
  • HSQLDB (Operations are called BITAND, BITOR, BITXOR)

4. A bit disappointing, for once

Usually the winner in these comparisons, here’s now a loser. It only supports the and (&) operation:
  • Oracle (Operation is called BITAND)

5. Out of the game

And finally, the usual suspects that lack bitwise functionality entirely
  • Ingres (Has hard-to-use BIT_AND and BIT_OR support. Input and output types have to be converted several times, so it doesn’t count)
  • Derby (No bitwise operations whatsoever)

Simulation in jOOQ

As usual, jOOQ hides such incompatibility facts from the developers where possible. The API is simple. Call upon any Field<?>:

    Field<Integer> bitCount();
    Field<T> bitNot();

    // The below methods are overloaded to also support field parameters
    Field<T> bitAnd(Number value);
    Field<T> bitNand(Number value);
    Field<T> bitOr(Number value);
    Field<T> bitNor(Number value);
    Field<T> bitXor(Number value);
    Field<T> bitXNor(Number value);
    Field<T> shl(Number value);
    Field<T> shr(Number value);

Here’s how these methods are simulated:


So MySQL’s bit_count() function can be simulated in most databases using this algorithm. In this case, for TINYINT data types. It’ll get quite mad for BIGINT’s:

SELECT (my_field &  1 )      +
       (my_field &  2 ) >> 1 +
       (my_field &  4 ) >> 2 +
       (my_field &  8 ) >> 3 +
       (my_field &  16) >> 4 +
       (my_field & 128) >> 7
FROM my_table

There’s also quite a freaky method in Java’s Integer.bitCount(int) and Long.bitCount(long) methods. It’s too freaky for me to understand, so I didn’t check if that’s the same as what MySQL does:

public static int bitCount(int i) {
    // HD, Figure 5-2
    i = i - ((i >>> 1) & 0x55555555);
    i = (i & 0x33333333) + ((i >>> 2) & 0x33333333);
    i = (i + (i >>> 4)) & 0x0f0f0f0f;
    i = i + (i >>> 8);
    i = i + (i >>> 16);
    return i & 0x3f;

Should you have a better idea, here’s where you could place it and score some reputation:

Left / Right Shifts

Simulated in DB2, H2, HSQLDB, Ingres, Oracle, SQL Server, Sybase ASE, Sybase SQL Anywhere. This can obviously be done by multiplication / division (running the risk of overflows). Here’s how

-- a << b
a * power(2, b)

-- a >> b
a / power(2, b)

If unavailable (Derby, SQLite), the power function is already simulated by jOOQ using the following formula:

-- power(a, b)
exp(ln(a) * b)

Not / Bitwise inverse

Simulated in H2, HSQLDB, Ingres, Oracle. This can be done arithmetically, by calculating

-- ~a, or bitnot(a)
-a - 1

All the rest

Simulated in Oracle. I’ve found something for that case, too:

-- a | b or bitor(a, b)
a - (a & b) + b

-- a ^ b or bitxor(a, b)
(a | b) - (a & b)

-- or also
~(a & b) & (a | b)


Once more, a slim layer of SQL abstraction proves to be very powerful to keep you from doing highly repetitive tasks. So download jOOQ right now, if you haven’t already! :-)

2 thoughts on “The comprehensive SQL bitwise operations compatibility list

  1. Great article.
    I think you’d be a great contributor for DZone’s MVB program (

    If you’re interested, send me and email and we can discuss further.

    (Editor @ JavaLobby)

Leave a Reply