在BigQuery中实现Oracle BitTest位查询功能的原生方案咨询

BigQuery doesn’t include a built-in BITTEST function like Oracle, but you can easily replicate its behavior using native bitwise operations. Here’s how to do it:

Core Logic #

The trick relies on two simple bitwise operations:

1. Right shift (>>): Move your numeric value right by BitPos - 1 positions (since Oracle’s BitPos starts counting from 1, not 0). This shifts the target bit to the lowest position.
2. Bitwise AND (&): Compare the shifted value with 1—this extracts the target bit’s value, returning 1 if the bit is set, 0 otherwise.

Example Query #

Using your sample value 1099511627780 (binary 10000000000000000000000000000000000000100):

WITH sample_values AS (
  SELECT 1099511627780 AS numeric_value
)
SELECT
  numeric_value,
  (numeric_value >> (1 - 1)) & 1 AS bit_pos_1, -- Returns 0
  (numeric_value >> (2 - 1)) & 1 AS bit_pos_2, -- Returns 0
  (numeric_value >> (3 - 1)) & 1 AS bit_pos_3  -- Returns 1
FROM sample_values;

Create a Reusable Custom Function #

To make this as convenient as Oracle’s BITTEST, wrap the logic in a custom function:

CREATE OR REPLACE FUNCTION `your_project.your_dataset.bittest`(value INT64, bit_pos INT64)
RETURNS INT64
AS ((value >> (bit_pos - 1)) & 1);

You can then use it exactly like Oracle’s version:

SELECT bittest(1099511627780, 3) AS test_result; -- Returns 1

Notes #

• This works with INT64 values (BigQuery’s standard integer type). If you’re using other numeric types, cast them to INT64 first.
• If you pass a BitPos larger than the number of bits in the value, the function will return 0 (since those higher bits don’t exist).

内容的提问来源于stack exchange，提问作者Lev Savranskiy