Is it possible to use BITAND operator in BigQuery with Non Integer values?

Question

I am using BITAND (&) operator in BigQuery

select (cast val as INT64) & pow(2,63) = pow(2,63)

The value for the field Val and pow(2,63) is exceeding the maximum value of integer and hence BITAND is not working.

I typecasted it to other data types but they are not working with BITAND operator

Any suggestions?

Answer 1

Try splitting and processing, casting both values to int64. Also, note that Bitwise and operator throws an error if X and Y are bytes of different lengths.

WITH CTE AS (
  SELECT
    CAST(val AS INT64) AS val,
    CAST(POW(2, 63) AS INT64) AS bitmask
  FROM
    WhateverTable)
SELECT
  val,
  bitmask,
  IF(val & bitmask = bitmask, TRUE, FALSE) AS result
FROM
  CTE;

https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#bitwise_operators