[HOWTO]ORACLE中如何實現ASC字符串和16進制串互相轉換
2024-08-29 13:45:09
供稿：網友
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:=sTmptrim(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:=sTmpchr(to_number(substr(sIn,i,4),'XXXX'));
i:=i+4;
else
sTmp:=sTmpchr(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:=sTmpchr(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么
