[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麼