sqlplsqlprestoapache-supersettrino

PL/SQL to Superset/Trino - convert hexadecimal funcion

I'm trying to convert the following pl/sql statement to use in Superset/Trino:

select to_number(substr('D0023377B23DB783',-16), 'XXXXXXXXXXXXXXXX') from dual;

result: 14988599054092911360

In trino/superset I'm using the following statement but it doesn't return the right output

select from_base(substr('D0023377B23DB783', 2), 16) ;

return: 619539137935235 return expected: 14988599099026945923

Can anyone help me please?

Thanks in advance, Ana

pl sql select to_number(substr('D0023377B23DB783',-16), 'XXXXXXXXXXXXXXXX') from dual;

result: 14988599054092911360

superset/trino

select from_base(substr('D0023377B23DB783', 2), 16) ;

return: 619539137935235 return expected: 14988599099026945923

Solution

  • You are throwing away some meaningful digits, so your calculation becomes invalid. Ideally you should be able to convert the number as is:

    select from_base('D0023377B23DB783', 16) ;

    But since from_base returns BIGINT this number is too big (see Presto fails to read hexadecimal string: Not a valid base-16 number). One trick is to switch to DECIMAL and parse the number in parts. Something like the following can give you an idea:

    select from_base('D0', 16) * cast(power(16, 14) as decimal) 
    + from_base('023377B23DB783', 16);

    Output:

            _col0         
----------------------
 14988599099026945923