How to implement an ASC string and 16 credit translation in Oracle
Welcome everyone to communicate with me: enhydraboy enhydra_boy@tom.com
Welcome to reprint, please keep this statement, thank you!
Below is the summary of the problem of the 9CBS forum before, I hope to be useful to everyone.
ASC string ==> 16 credit string
Create or Replace function asctohex (sin in varchar2) Return VARCHAR2ISSTMP VARCHAR2 (4000); I Integer; Begini: = 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 credit ==> ASC string to distinguish the character set, as an example in Chinese characters, for the database character set is Chinese, to ensure that it is possible to turn back, it must be different.
Database character set is Chinese
Create Or Replace Function Hextoasc (Sin In Varchar2) Return VARCHAR2ISSTMP VARCHAR2 (4000); I Integer; X Integer; Begini: = 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 (SINI) RETURN VARCHAR2ISSTMP VARCHAR2 (4000); I Integer; Begini: = 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 good ABC?) ---------------------------------------------------------------------------------------- -------------------------------------------------- ---------- Hello ABC?
