[HOWTO]ORACLE中如何实现ASC字符串和16进制串互相转换

oracle中如何实现asc字符串和16进制串互相转换

欢迎大家同我交流：enhydraboy   enhydra_boy@tom.com

下面是笔者以前回答csdn论坛上问题的总结，希望对大家有用。

asc字符串==>16进制串

create or replace function asctohex(sin in varchar2)
return varchar2
is
stmp varchar2(4000);
i integer;
begin
i:=1;
stmp:='';
for i in 1..length(sin) loop
   stmp:=stmp||trim(to_char(ascii(substr(sin,i,1)),'xxxx'));
end loop;
return stmp;
end;
/

16进制串==>asc字符串
要区分字符集，以中文字符为例，对于数据库字符集是中文，要保证能够正确转回来，要有所不同。

数据库字符集是中文

create or replace function hextoasc(sin in varchar2)
return varchar2
is
stmp varchar2(4000);
i integer;
x integer;
begin
i:=1;
stmp:='';
loop
   exit when i>length(sin);
   x:=to_number(substr(sin,i,2),'xxxx');
   if x>128 then
     stmp:=stmp||chr(to_number(substr(sin,i,4),'xxxx'));
     i:=i+4;
   else
     stmp:=stmp||chr(to_number(substr(sin,i,2),'xxxx'));
     i:=i+2;
   end if;
  
end loop;
return stmp;
end;
/

数据库字符集是英文

create or replace function hextoasc(sin in varchar2)
return varchar2
is
stmp varchar2(4000);
i integer;
begin
i:=1;
stmp:='';
loop
   exit when i>length(sin);
   stmp:=stmp||chr(to_number(substr(sin,i,2),'xxxx'));
   i:=i+2;
end loop;
return stmp;
end;
/

测试，如下：

sql>select hextoasc(asctohex('你好abc么')) from dual;
hextoasc(asctohex('拿好abc么'))
--------------------------------------------------------------------------------
你好abc么