[HOWTO]ORACLE中如何?ASC字串和16進位串互相轉換

oracle|轉換|字串
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麼