I have a 160 chars bit string and I need to have an integer array that stores the position of the bits that have a value of 1.
Example:
bitstring = '00110101'
array = [3,4,6,8]
Is it possible to do this just with SQL or do I need to define a PL/SQL function or something like that?
It's assuredly possible to write it in SQL. Here's a starting point:
select array(
select substring(str from i for 1) as bit
from generate_series(1, length(str)) as i
where bit = '1'
);
You might want to wrap that in a pl/sql function regardless, though, so to avoid duplicating code all over the place.
Working function:
create or replace function get_bit_positions(varbit) returns bit[] as $$
select array(
select substring($1 from i for 1) as bit
from generate_series(1, length($1)) as i
where substring($1 from i for 1) = '1'
);
$$ language sql immutable;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With