[HOWTO] How to implement the ASC string and 16-string conversion in Oracle

oracle| Conversion | string
How to implement the ASC string and 16-string conversion in Oracle

Welcome everyone to communicate with me: Enhydraboy enhydra_boy@tom.com

Welcome to reprint, please keep this statement, thank you!

The following is the author's previous answer to the CSDN Forum on the summary of questions, I hope to be useful.

ASC string ==>16 into string

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 Binary string ==>ASC strings
To differentiate between character sets, take Chinese characters as an example, to make sure that the database character set is in Mandarin and to be able to turn back correctly.

The database character set is Chinese

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;
/


Database character set is in English

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;
/

Test, as follows:

Sql>select Hextoasc (asctohex (' Hello ABC ') from dual;
HEXTOASC (Asctohex (' take ABC) ")
--------------------------------------------------------------------------------
Hello ABC?