Function in Postgres to convert a varchar to a big integer

Question

I have a varchar column in Postgres 8.3 that holds values like: '0100011101111000'

I need a function that would consider that string to be a number in base 2 and spits out the numeric in base 10. Makes sense?

So, for instance:

'000001' -> 1.0

'000010' -> 2.0

'000011' -> 3.0

Thanks!

Answer 1

Cast to a bit string then to an integer.

An example: '1110'::bit(4)::integer -> 14

Though you had varying length examples, and were after bigint, so instead use bit(64) and pad the input with zeroes using the lpad function.

lpad('0100011101111000',64,'0')::bit(64)::bigint

Here's a complete example...

create temp table examples (val varchar(64));

insert into examples values('0100011101111000');
insert into examples values('000001');
insert into examples values('000010');
insert into examples values('000011');

select val,lpad(val,64,'0')::bit(64)::bigint as result from examples;

The result of the select is:

       val        | result 
------------------+--------
 0100011101111000 |  18296
 000001           |      1
 000010           |      2
 000011           |      3
(4 rows)