CODEWITHSUNDEEP
oracle-databaseplsql
frgtv10
frgtv10

Reputation: 5450

Convert hex to string

I want to use HEXTORAW() to get the char value from the ASCII HEX code '30'. ASCII HEX 30 should return varchar '0'.

How to to that? Is HEXTORAW() the right function?

Upvotes: 3

Views: 28456

Answers (3)

Maarten Malaise
Maarten Malaise

Reputation: 11

I had some difficulties using characters from the extended ascii range (ISO Latin‑1 ; ISO-8859‑1). Solved it like this:

select chr(to_number('D6','xx') using NCHAR_CS) from dual

where hex value 'D6' matches (extended) ascii value 214 or character 'Ö'

or in a function

create or replace function hex_to_ascii(TEXT_IN varchar2) 
RETURN varchar2
as
  TEXT_OUT varchar2(200);
  TEXT_MAN varchar2(200):=TEXT_IN;
BEGIN
  while length(TEXT_MAN)>0
  LOOP
    TEXT_OUT:=TEXT_OUT||chr(to_number(substr(TEXT_MAN,0,2),'xx') using NCHAR_CS);
    TEXT_MAN:=substr(TEXT_MAN,3);
  END LOOP;
  return TEXT_OUT; 
END;

The function can be used like this:

select hex_to_ascii('30D6D0D1C7DC') from dual

The input hex string '30D6D0D1C7DC' will be converted to '0ÖÐÑÇÜ'

I hope that this can be of use to others

Upvotes: 1

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

You can also use CHR (for single characters):

SQL> select chr(to_number('30', 'XX')) from dual;

CHR(TO_NUMBER('30','XX'))
-------------------------
0

Upvotes: 2

Nick Krasnov
Nick Krasnov

Reputation: 27251

You could use utl_raw package and cast_to_varchar2() function specifically:

select utl_raw.cast_to_varchar2(hextoraw('30')) as res
  from dual

result:

RES
-----
0

Upvotes: 9

Related Questions