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么
延伸阅读
文章来源于领测软件测试网 https://www.ltesting.net/
