有一批老的数据需要导入到新的系统，需求上有所变更，要对原来存储的二进制数据转为十进制数据，网上搜了下没有现成的，就按原理写了一下，代码实现如下：
一、代码主体
@H_301_6@CREATE OR REPLACE FUNCTION bin_to_dec(i_bin text)
RETURNS numeric AS
$BODY$
declare
v_len int;
v_length int;
v_pos int;
v_rec text;
v_value numeric;
v_record int;
begin
v_value = 0;
--判断是否二进制数据，否则直接退出
select length(trim(translate(i_bin,'[.01]',''))) into v_len;
if v_len >0 then
return 99999999;
exit;
end if;
--判断小数点是否只有一个,多于1个则退出
select length(translate(i_bin,'.'||i_bin,'.')) into v_len;
if v_len > 1 then
return 88888888;
exit;
--整数部分,以下不含小数点
elsif v_len < 1 then
select length(i_bin) into v_length;
for v_rec in select regexp_split_to_table(i_bin,'') loop
v_record := v_rec::int;
v_value := v_value + v_record * power(2,v_length-1);
v_length := v_length-1;
end loop;
return v_value;
exit;
--以下是含小数点的算法
else
select position('.' in i_bin),length(i_bin) into v_pos,v_len;
select length(substr(i_bin,1,v_pos-1)) into v_length;
for v_rec in select regexp_split_to_table(substr(i_bin,v_pos-1),v_length-1);
v_length := v_length-1;
end loop;
v_length := -1;
for v_rec in select regexp_split_to_table(substr(i_bin,v_pos+1,v_len),'') loop
v_record := v_rec::int;
v_value := v_value + v_record * power(2,v_length);
v_length := v_length-1;
end loop;
return v_value;
exit;
end if;
return v_value;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
二、实现
@H_301_6@postgres=# select bin_to_dec('11.01');
bin_to_dec
------------
3.25
(1 row)
postgres=# select bin_to_dec('110');
bin_to_dec
------------
6
(1 row)
postgres=# select bin_to_dec('0.11');
bin_to_dec
------------
0.75
(1 row)
