CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user8024280
user8024280

Reputation:

Sql conversion of ascii value to ascii string

In a following select:

select value from A;

I have this varchar output:30313233 which after conversion of each character based on its ascii code representation equals 1234.

Is there any way how to make this conversion automatically on SQL level? I am using Oracle SQL.

Upvotes: 0

Views: 485

Answers (1)

APC
APC

Reputation: 146239

This solution uses a WITH clause subquery to chop the string into tokens of two digit hex values. That output feeds into the main query which converts the hex value into raw and then into characters, and uses LISTAGG() to stitch them back together.

with cte as ( 
    select substr('30313233', (level*2)-1, 2) as tkn
           ,level as id
    from dual
    connect by level <= length('30313233')/2
    ) 
select listagg(utl_raw.cast_to_varchar2(hextoraw(tkn))) within group (order by id)
from cte

There is a demo on db<>fiddle. Obviously your version of this code will rewrite the subquery to select the value from your table.

Incidentally, the input string is the hex representation of 0123. The ASCII representation of 1234 would be 49505152 and requires a slightly different solution:

with cte as ( 
    select substr('49505152', (level*2)-1, 2) as tkn
           ,level as id
    from dual
    connect by level <= length('49505152')/2
    ) 
select listagg(chr(tkn)) within group (order by id)
from cte

Upvotes: 1

Related Questions