Function in Postgres to convert a varchar to a big integer

postgresql

Solution

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)

Problem

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!

Original source