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

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

欢迎大家同我交流：enhydraboy   [email protected]

欢迎转载，请保留本声明，谢谢！

下面是笔者以前回答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么