Convert Bit String To Array in PostgreSQL

Question

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?

The position count starts at 1, not at 0 – Topo Apr 25 '13 at 18:01 — Topo, Apr 25 '13 at 18:01

score 6 · Answer 1 · edited Apr 13 '17 at 12:42

Working version:

WITH x AS (SELECT '00110101'::varbit AS b)
SELECT array_agg(i)
FROM  (SELECT b, generate_series(1, length(b)) AS i FROM x) y
WHERE  substring(b, i, 1) = '1';

Simpler once you convert the varbit to text[]. Cast to text and run string_to_array().

Then you can use generate_subscripts() and pick array elements by index:

WITH x AS (SELECT string_to_array('00110101'::varbit::text, NULL) AS b)
SELECT array_agg(i)
FROM  (SELECT b, generate_subscripts(b,1) AS i FROM x) y
WHERE b[i] = '1'

Details in this related question on dba.SE.

Denis de Bernardy · Accepted Answer · 2013-04-26T10:43:58.347

5

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;

edited Apr 26 '13 at 10:43

answered Apr 25 '13 at 18:16

Denis de Bernardy

75,850
13
131
154

@Topo: This answer does not work. Illegal reference to a column aliases in the `WHERE` clause, illegal reference to `str`. Why would you accept it? BTW: pl/sql is the name of Oracle's procedural language. Probably plpgsql. – Erwin Brandstetter Apr 25 '13 at 23:04
pl/sql is also the name of plain sql functions in postgres. – Denis de Bernardy Apr 26 '13 at 10:36
1

Those are just called "SQL functions". There is no PL/SQL in PostgreSQL. "PL" is reserved for procedural languages. – Erwin Brandstetter Apr 26 '13 at 11:42
1

@ErwinBrandstetter I know it does not runs, but I just had to change the `bit` in the last line for `substring($1 from i for 1)`. I accepted the answer because it answered the question, it was only the syntax that was wrong – Topo Apr 30 '13 at 02:33
@Topo: If you found the answer needed improvements to make it work, don't just silently accept the broken version. Other people will find it and waste their time in wonder why it wouldn't work for them. Give feedback or fix the answer. As you can see, Denis happily improved his answer. – Erwin Brandstetter Apr 30 '13 at 04:56

Convert Bit String To Array in PostgreSQL

2 Answers2