活动专区

活动公告

上车！GBase 8a开发者联盟发车了 技术福利、人脉资源全都有！

认证培训专区

认证培训资料

GBase 8s GDCA 认证培训课前准备

GBase 8a

其他

物化视图报错：references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

01-GBase 8s 事务型数据库 安装与卸载

GBase 8s

适配迁移

[GBase 8s 教程]GBase 8s 常用函数、表达式

用户可能会用到二进制、八进制、十进制、十六进制之间的相互转换。
### 二进制转换
#### 二进制转八进制





```sql
-- 函数名称: 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;
```
#### 二进制转十进制
```sql
-- 函数名称: bin_to_dec
-- 输入参数：二进制字符串
-- 输出结果：十进制字符串
drop function if exists bin_to_dec;
create function bin_to_dec(pstr varchar(255))
returns varchar(255) with (not variant)
  define curpos int;
  define vint bigint;
  on exception
    return null;
  end exception;
  
  let vint = 0;
  for curpos = 1 to length(pstr)
    let vint = vint + substr(pstr,curpos,1) * power(2, length(pstr) - curpos);
  end for;
  return vint::varchar(255);
end function;
```
#### 二进制转十六进制
```sql
-- 函数名称: 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 vstr varchar(255);
  define vval varchar(255);
  on exception
    return null;
  end exception;
  
  let modstrlen = mod(length(pstr),4);
  let vstr = lpad(pstr, (4 - modstrlen) + length(pstr), '0');
  let vval = '';
  for curpos = 1 to length(vstr) 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;
```
### 八进制转换
#### 八进制转二进制
```sql
-- 函数名称: 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 vval varchar(255);
  on exception
    return null;
  end exception;

  let vval = '';
  for curpos = 1 to length(pstr)
    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;
```
#### 八进制转十进制
```sql
-- 函数名称: oct_to_dec
-- 输入参数：八进制字符串
-- 输出结果：十进制字符串
drop function if exists oct_to_dec;
create function oct_to_dec(pstr varchar(255))
returns varchar(255) with (not variant)
  define curpos int;
  define vint bigint;
  on exception
    return null;
  end exception;
  
  let vint = 0;
  for curpos = 1 to length(pstr)
    let vint = vint + substr(pstr,curpos,1) * power(8, length(pstr) - curpos);
  end for;
  return vint::varchar(255);
end function;
```
#### 八进制转十六进制
```sql
-- 函数名称: 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;
```
### 十进制转换
#### 十进制转二进制
调用系统的hex()函数
```sql
-- 函数名称: 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),3),'0')));
end function;
```
#### 十进制转八进制
调用系统的hex()函数
```sql
-- 函数名称: 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),3),'0')));
end function;
```
#### 十进制转十六进制
调用系统的hex()函数
```sql
-- 函数名称: 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),3),'0'));
end function;
```
### 十六进制转换
#### 十六进制转二进制
```sql
-- 函数名称: 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 vval varchar(255);
  on exception
    return null;
  end exception;
  
  let vval = '';
  for curpos = 1 to length(pstr)
    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;
```
#### 十六进制转八进制
```sql
-- 函数名称: 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;
```
#### 十六进制转十进制
```sql
-- 函数名称: 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 int;
  define curval int;
  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 + curval * power(16, length(pstr) - curpos);
  end for;
  return vint::varchar(255);
end function;
```

GBase 8s数据库中的二进制、八进制、十进制、十六进制相互转换函数

GBase 8s数据库中的二进制、八进制、十进制、十六进制相互转换函数

评论

热门帖子