用户可能会用到二进制、八进制、十进制、十六进制之间的相互转换。
注：支持0至bigint（9223372036854775807）的间的进制转换

次方函数（bigint）

-- 函数名称：bigint_power
-- 输入参数：x, y
-- 输出结果：x^y
drop function if exists bigint_power;
create function bigint_power(p1 bigint, p2 bigint, p3 bigint default 1)
returns bigint with (not variant);
  define v_bigint bigint;
  define v_int bigint;
  on exception
    return null;
  end exception;
  if p1 = 0 or p3 = 0 then
    return 0;
  end if;
  if p1 = 1 then
    return p3;
  end if;
  let v_bigint = p1;
  if p2 = 0 then
    let v_bigint = 1;
  elif p2 = 1 then
    let v_bigint = p1;
  else
    for v_int = 2 to p2
      let v_bigint = v_bigint * p1;
    end for;
  end if;
  return v_bigint * p3;
end function;

二进制转换

二进制转八进制

-- 函数名称: bin_to_oct
-- 输入参数：二进制字符串
-- 输出结果：八进制字符串
drop function if exists bin_to_oct;
create function bin_to_oct(pstr varchar(255))
returns varchar(255) with (not variant)
  define modstrlen int;
  define curpos int;
  define vstr varchar(255);
  define vval varchar(255);
  on exception
    return null;
  end exception;
   
  let modstrlen = mod(length(pstr),3);
  let vstr = lpad(pstr, (3 - modstrlen) + length(pstr), '0');
  let vval = '';
  for curpos = 1 to length(vstr) step 3
    case substr(vstr,curpos,3)
      when '000' then let vval = vval || '0';
      when '001' then let vval = vval || '1';
      when '010' then let vval = vval || '2';
      when '011' then let vval = vval || '3';
      when '100' then let vval = vval || '4';
      when '101' then let vval = vval || '5';
      when '110' then let vval = vval || '6';
      when '111' then let vval = vval || '7';
    end case;
  end for;
  return ltrim(vval,'0');
end function;

二进制转十进制

-- 函数名称: bin_to_dec
-- 输入参数：二进制字符串
-- 输出结果：十进制字符串
drop function if exists bin_to_dec;
create function bin_to_dec(pstr varchar(255))
returns varchar(255) with (not variant)
  return hex_to_dec(bin_to_hex(pstr));
end function;

二进制转十六进制

-- 函数名称: bin_to_hex
-- 输入参数：二进制字符串
-- 输出结果：十六进制字符串
drop function if exists bin_to_hex;
create function bin_to_hex(pstr varchar(255))
returns varchar(255) with (not variant)
  define modstrlen int;
  define curpos int;
  define vlen int;
  define vstr varchar(255);
  define vval varchar(255);
  on exception
    return null;
  end exception;
   
  let vlen = length(pstr);
  let modstrlen = mod(vlen,4);
  let vstr = lpad(pstr, (4 - modstrlen) + vlen, '0');
  let vval = '';
  for curpos = 1 to vlen step 4
    case substr(vstr,curpos,4)
      when '0000' then let vval = vval || '0';
      when '0001' then let vval = vval || '1';
      when '0010' then let vval = vval || '2';
      when '0011' then let vval = vval || '3';
      when '0100' then let vval = vval || '4';
      when '0101' then let vval = vval || '5';
      when '0110' then let vval = vval || '6';
      when '0111' then let vval = vval || '7';
      when '1000' then let vval = vval || '8';
      when '1001' then let vval = vval || '9';
      when '1010' then let vval = vval || 'a';
      when '1011' then let vval = vval || 'b';
      when '1100' then let vval = vval || 'c';
      when '1101' then let vval = vval || 'd';
      when '1110' then let vval = vval || 'e';
      when '1111' then let vval = vval || 'f';
    end case;
  end for;
  return ltrim(vval,'0');
end function;

八进制转换

八进制转二进制

-- 函数名称: oct_to_bin
-- 输入参数：八进制字符串
-- 输出结果：二进制字符串
drop function if exists oct_to_bin;
create function oct_to_bin(pstr varchar(255))
returns varchar(255) with (not variant)
  define curpos int;
  define vlen int;
  define vval varchar(255);
  on exception
    return null;
  end exception;
 
  let vval = '';
  let vlen = length(pstr);
  for curpos = 1 to vlen
    case substr(pstr,curpos,1)
      when '0' then let vval = vval || '000';
      when '1' then let vval = vval || '001';
      when '2' then let vval = vval || '010';
      when '3' then let vval = vval || '011';
      when '4' then let vval = vval || '100';
      when '5' then let vval = vval || '101';
      when '6' then let vval = vval || '110';
      when '7' then let vval = vval || '111';
    end case;
  end for;
  return ltrim(vval,'0');
end function;

八进制转十进制

-- 函数名称: oct_to_dec
-- 输入参数：八进制字符串
-- 输出结果：十进制字符串
drop function if exists oct_to_dec;
create function oct_to_dec(pstr varchar(255))
returns varchar(255) with (not variant)
  return hex_to_dec(oct_to_hex(pstr));
end function;

八进制转十六进制

-- 函数名称: oct_to_hex
-- 输入参数：八进制字符串
-- 输出结果：十六进制字符串
drop function if exists oct_to_hex;
create function oct_to_hex(pstr varchar(255))
returns varchar(255) with (not variant)
  return bin_to_hex(oct_to_bin(pstr));
end function;

十进制转换

十进制转二进制

-- 函数名称: dec_to_bin
-- 输入参数：十进制字符串
-- 输出结果：二进制字符串
drop function if exists dec_to_bin;
create function dec_to_bin(pstr varchar(255))
returns varchar(255) with (not variant)
  return hex_to_bin(lower(ltrim(substr(hex(pstr::bigint),3),'0')));
end function;

十进制转八进制

-- 函数名称: dec_to_oct
-- 输入参数：十进制字符串
-- 输出结果：八进制字符串
drop function if exists dec_to_oct;
create function dec_to_oct(pstr varchar(255))
returns varchar(255) with (not variant)
  return hex_to_oct(lower(ltrim(substr(hex(pstr::bigint),3),'0')));
end function;

十进制转十六进制

-- 函数名称: dec_to_hex
-- 输入参数：十进制字符串
-- 输出结果：十六进制字符串
drop function if exists dec_to_hex;
create function dec_to_hex(pstr varchar(255))
returns varchar(255) with (not variant)
  return lower(ltrim(substr(hex(pstr::bigint),3),'0'));
end function;

十六进制转换

十六进制转二进制

-- 函数名称: hex_to_bin
-- 输入参数：十六进制字符串
-- 输出结果：二进制字符串
drop function if exists hex_to_bin;
create function hex_to_bin(pstr varchar(255))
returns varchar(255) with (not variant)
  define curpos int;
  define vlen int;
  define vval varchar(255);
  on exception
    return null;
  end exception;
   
  let vval = '';
  let vlen = length(pstr);
  for curpos = 1 to vlen
    case substr(pstr,curpos,1)
      when '0' then let vval = vval || '0000';
      when '1' then let vval = vval || '0001';
      when '2' then let vval = vval || '0010';
      when '3' then let vval = vval || '0011';
      when '4' then let vval = vval || '0100';
      when '5' then let vval = vval || '0101';
      when '6' then let vval = vval || '0110';
      when '7' then let vval = vval || '0111';
      when '8' then let vval = vval || '1000';
      when '9' then let vval = vval || '1001';
      when 'a' then let vval = vval || '1010';
      when 'b' then let vval = vval || '1011';
      when 'c' then let vval = vval || '1100';
      when 'd' then let vval = vval || '1101';
      when 'e' then let vval = vval || '1110';
      when 'f' then let vval = vval || '1111';
    end case;
  end for;
  return ltrim(vval,'0');
end function;

十六进制转八进制

-- 函数名称: hex_to_oct
-- 输入参数：十六进制字符串
-- 输出结果：八进制字符串
drop function if exists hex_to_oct;
create function hex_to_oct(pstr varchar(255))
returns varchar(255) with (not variant)
  return bin_to_oct(hex_to_bin(pstr));
end function;

十六进制转十进制

-- 函数名称: hex_to_dec
-- 输入参数：十六进制字符串
-- 输出结果：十进制字符串
drop function if exists hex_to_dec;
create function hex_to_dec(pstr varchar(255))
returns varchar(255) with (not variant)
  define curpos bigint;
  define curval bigint;
  define vint bigint;
  on exception
    return null;
  end exception;
 
  let vint = 0;
  for curpos = 1 to length(pstr)
    let curval = 0;
    case lower(substr(pstr,curpos,1)) 
      when 'a' then let curval = 10;
      when 'b' then let curval = 11;
      when 'c' then let curval = 12;
      when 'd' then let curval = 13;
      when 'e' then let curval = 14;
      when 'f' then let curval = 15;
      else let curval = substr(pstr,curpos,1);
    end case;
    let vint = vint + bigint_power(16, length(pstr) - curpos, curval);
  end for;
  return vint::varchar(255);
end function;

进制制转换函数

-- 函数名称：conv
-- 输入参数：源进制字符串, 源进制, 目标进制
-- 输出结果：目标进制字符串
drop function if exists conv;
create function conv(pstr varchar(255), p1 int, p2 int default 10)
returns varchar(255) with (not variant)
  define vval varchar(255);
  on exception
    return null;
  end exception;

  case p1 
    when 2  then let vval = case p2 when 8 then bin_to_oct(pstr) when 10 then bin_to_dec(pstr) when 16 then bin_to_hex(pstr) else pstr end;
    when 8  then let vval = case p2 when 2 then oct_to_bin(pstr) when 10 then oct_to_dec(pstr) when 16 then oct_to_hex(pstr) else pstr end;
    when 10 then let vval = case p2 when 2 then dec_to_bin(pstr) when 8  then dec_to_oct(pstr) when 16 then dec_to_hex(pstr) else pstr end;    
    when 16 then let vval = case p2 when 2 then hex_to_bin(pstr) when 8  then hex_to_oct(pstr) when 10 then hex_to_dec(pstr) else pstr end;    
    else let vval = null;
  end case;
  return vval;
end function;

测试语句

-- 测试语句
select dec_to_hex('9223372036854775807') from dual;
-- 7fffffffffffffff
select dec_to_bin('9223372036854775807') from dual;
-- 111111111111111111111111111111111111111111111111111111111111111
select dec_to_oct('9223372036854775807') from dual;
-- 777777777777777777777

select hex_to_dec('7fffffffffffffff') from dual;
-- 9223372036854775807
select hex_to_bin('7fffffffffffffff') from dual;
-- 111111111111111111111111111111111111111111111111111111111111111
select hex_to_oct('7fffffffffffffff') from dual;
-- 777777777777777777777

select bin_to_oct('111111111111111111111111111111111111111111111111111111111111111') from dual;
-- 777777777777777777777
select bin_to_dec('111111111111111111111111111111111111111111111111111111111111111') from dual;
-- 9223372036854775807
select bin_to_hex('111111111111111111111111111111111111111111111111111111111111111') from dual;
-- 7fffffffffffffff

select oct_to_bin('777777777777777777777') from dual;
-- 111111111111111111111111111111111111111111111111111111111111111
select oct_to_dec('777777777777777777777') from dual;
-- 9223372036854775807
select oct_to_hex('777777777777777777777') from dual;
-- 7fffffffffffffff

select conv('9223372036854775807',10,2) from dual;
select conv('9223372036854775807',10,8) from dual;
select conv('9223372036854775807',10,10) from dual;
select conv('9223372036854775807',10) from dual;
select conv('9223372036854775807',10,16) from dual;

select conv('7fffffffffffffff',16,2) from dual;
select conv('7fffffffffffffff',16,8) from dual;
select conv('7fffffffffffffff',16,10) from dual;
select conv('7fffffffffffffff',16) from dual;
select conv('7fffffffffffffff',16,16) from dual;

select conv('111111111111111111111111111111111111111111111111111111111111111',2,2) from dual;
select conv('111111111111111111111111111111111111111111111111111111111111111',2,8) from dual;
select conv('111111111111111111111111111111111111111111111111111111111111111',2,10) from dual;
select conv('111111111111111111111111111111111111111111111111111111111111111',2) from dual;
select conv('111111111111111111111111111111111111111111111111111111111111111',2,16) from dual;

select conv('777777777777777777777',8,2) from dual;
select conv('777777777777777777777',8,8) from dual;
select conv('777777777777777777777',8,10) from dual;
select conv('777777777777777777777',8) from dual;
select conv('777777777777777777777',8,16) from dual;

注
本文件于2023-10-17更新，原函数因使用power()函数导致精度溢出，改使用自定bigint_power()函数。
原始SQL
GBase_8s自定义进制转换函数conv_20231017.zip