Count the location of "1"s in binary number RRS feed

  • Question

  • User-721945135 posted

    I'm using Oracle SQL and I have one query like this select bin_num as BINARY_NUMBER from table1
    this bin_num will display binary number with 25 digits like "0001000000100001000000000" and I want to display the result based on the location of "1"s in my result. 
    For example, "1"s is locate at number 4, 11 & 16. So my result will display as below table:

    0001000000100001000000000 4
    0001000000100001000000000 11
    0001000000100001000000000 16

    If using INSTR function, it work find if the bin_num is "0001000000000000000000000" which it will display "4" as a result.
    But what if the bin_num is "0001000000100001000000000" which contain 3 "1"s, it will only display "4" instead of "4, 11, 16" as a result.
    So what to do if I want to get "4, 11, 16" as result.

    Please guide me how to do this in SQL.

    Thanks in advance.

    Wednesday, November 23, 2011 7:37 PM

All replies

  • User269602965 posted

    use PLSQL loop and advance your INSTR pointer not to exceed 25


    Saturday, November 26, 2011 5:50 PM