0

I have a requirement to convert alphanumeric to numeric and vice-versa.

Example: If 'A2' is passed then I have written below query to convert it to numeric:

select sum(val) from (
  select power(36, loc - 1) *
  case when letter between '0'
  and '9'
  then to_number(letter)
  else 10 + ascii(letter) - ascii('A')
  end as val from(
    select substr(ip_str, length(ip_str) + 1 - level, 1) letter,
    level loc from(select 'A2'
      ip_str from dual) connect by level <= length(ip_str)
  )
); --sum(val) returns 362

How do I decode 362 back to 'A2'?

ℛɑƒæĿᴿᴹᴿ
  • 4,983
  • 4
  • 38
  • 58
Akira
  • 1
  • Does alphanumeric here mean base 36 ? If yes https://stackoverflow.com/questions/2568668/base-36-to-base-10-conversion-using-sql-only – pifor Jun 20 '20 at 18:50
  • No sorry I meant numeric to be base 36. I wanted to convert base 36 to alphanumeric. – Akira Jun 22 '20 at 14:37

1 Answers1

1

Base N Convert - this site describes algorithm. I implemented it as recursive query:

with 
  t(num) as (select 362 from dual),
  r(md, div, lvl) as (
    select mod(num, 36), floor(num/36), 1 from t union all
    select mod(div, 36), floor(div/36), lvl + 1 from r where div > 0)
select listagg(case when md > 9 then chr(ascii('A') - 10 + md) 
                    else to_char(md) 
               end) within group (order by lvl desc) b36
  from r

dbfiddle demo

Seems to work, I tested several values comparing results with online calculators. Theoretically you can use other bases, not only 36, algorithm is the same, but I did not test it.

Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24