CODEWITHSUNDEEP
sqloracle-databasealphanumeric
Akira
Akira

Reputation: 1

Converting alphanumeric to numeric and vice versa in oracle

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'?

Upvotes: 0

Views: 484

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

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.

Upvotes: 1

Related Questions