Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
nth bit value of what? – ypercubeᵀᴹ Oct 7 '13 at 12:09
of a number. 7 for here – Mohsen Kashi Oct 7 '13 at 12:10
That works. What are you looking for? – Colin 't Hart Oct 7 '13 at 13:31
simpler query! my original query is too big and complex. I need reduce its size for preventing confusion – Mohsen Kashi Oct 7 '13 at 14:10
up vote 2 down vote accepted

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);
share|improve this answer
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. – Colin 't Hart Oct 7 '13 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
share|improve this answer
It is same as my query! – Mohsen Kashi Oct 9 '13 at 7:18
How is this different from the query presented by the OP? – dezso Oct 9 '13 at 10:06

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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