Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

WITH a AS
     (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

2 Answers 2

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
as
(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
FROM a
share|improve this answer
1  
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

 
discard

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.