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:
BINARY_NUMBER
LOCATION_OF_"1"s
0001000000100001000000000
4
0001000000100001000000000
11
0001000000100001000000000
16
<div class="row">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.</div> <div class="row"></div>
ying87
Member
37 Points
61 Posts
Count the location of "1"s in binary number
Nov 23, 2011 11:37 PM|LINK
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:
<div class="row">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.</div> <div class="row"></div>
Please guide me how to do this in SQL.
</div>Thanks in advance.
Lannie
Contributor
3744 Points
731 Posts
Re: Count the location of "1"s in binary number
Nov 26, 2011 09:50 PM|LINK
use PLSQL loop and advance your INSTR pointer not to exceed 25