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!
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)
