How can I get nth bit value in oracle database? I am currently using this query:

     (SELECT 7 val
        FROM DUAL)
SELECT CASE BITAND (a.val, POWER (2, n-1))
          WHEN 0
             THEN 0
          ELSE 1
       END nth_bit
  FROM a
  • nth bit value of what? Oct 7, 2013 at 12:09
  • of a number. 7 for here
    – Nik Kashi
    Oct 7, 2013 at 12:10
  • That works. What are you looking for? Oct 7, 2013 at 13:31
  • simpler query! my original query is too big and complex. I need reduce its size for preventing confusion
    – Nik Kashi
    Oct 7, 2013 at 14:10

2 Answers 2


The shortest expression you can get is:

least(bitand(a, power(2, n-1)), 1)

where you want to find the nth bit of a.

The shortest query with this expression is:

select least(bitand(a, power(2, n-1)), 1) from dual;

From what I've read the above expression also works in PL/SQL, eg:

l_bit_n := least(bitand(a, power(2, n-1)), 1);
  • If this is for PL/SQL -- or if the cost of context switches between SQL and PL/SQL aren't important (ie you aren't querying millions of rows), then I would put the expression into its own PL/SQL function. Oct 7, 2013 at 14:36
with a
(select 8 as value,4 as bit_number  from dual)
SELECT CASE WHEN BITAND (a.value, POWER (2, a.bit-1))=POWER (2, a.bit -1)
            THEN 1
          ELSE 0
       END nth_bit
  • 1
    It is same as my query!
    – Nik Kashi
    Oct 9, 2013 at 7:18
  • 1
    How is this different from the query presented by the OP? Oct 9, 2013 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.