Base 36 to Base 10 conversion using SQL only

Question

A situation has arisen where I need to perform a base 36 to base 10 conversion, in the context of a SQL statement. There doesn't appear to be anything built into Oracle 9, or Oracle 10 to address this sort of thing. My Google-Fu, and AskTom suggest creating a pl/sql function to deal with the task. That is not an option for me at this point. I am looking for suggestions on an approach to take that might help me solve this issue.

To put this into a visual form...

WITH
Base36Values AS
(
    SELECT '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' myBase36 FROM DUAL
),
TestValues AS
(
    SELECT '01Z' BASE36_VALUE,
            71   BASE10_VALUE FROM DUAL
)
SELECT *
FROM Base36Values,
     TestValues

I am looking for something to calculate the value 71, based on the input 01Z. EDIT - that is backwards... given 01Z translate it to 71.

As a bribe, each useful answer gets a free upvote.

Thanks

Evil.

I'm just curious (I've always wanted find an excuse to use different bases to store data); what is the base 36 data used for? — David Murdoch, Apr 02 '10 at 19:21
And I'm curious why this has to be done in SQL, that's not really common. — Georg Schölly, Apr 02 '10 at 19:23
The advantage of base 36 is that you can use an embedded device which has a limited keyboard to provide input. Then, interpreting it as base36 instead as string is more efficient because of the size and moreover you can calculate with it using default functions. — Henri, Apr 02 '10 at 19:30
+1, one of the more interesting questions I've seen posed lately — DCookie, Apr 02 '10 at 20:30
@david It's a unique identifier from a legacy system. Just think of it as an encoded sequence number. @georg It's a political thing. Choose your battles and all that. @dcookie thank you.... — EvilTeach, Apr 02 '10 at 22:07

score 27 · Accepted Answer · edited Aug 31 '10 at 15:05

27

select sum(position_value) from
(
  select power(36,position-1) * case when digit between '0' and '9' 
                                     then to_number(digit)
                                     else 10 + ascii(digit) - ascii('A')
                                end
          as position_value
    from (
          select substr(input_string,length(input_string)+1-level,1) digit, 
                 level position
            from (select '01Z' input_string from dual)
            connect by level <= length(input_string)
         )
)

edited Aug 31 '10 at 15:05

EvilTeach

28,120
21
85
141

answered Apr 02 '10 at 19:28

Dave Costa

47,262
8
56
72

1

+1, saweeeeeet! Excellent illustration of the use of the connect by level syntax. – DCookie Apr 02 '10 at 20:29
Yes. Replace the base36 string, by a case statement. Thank you Dave. – EvilTeach Apr 02 '10 at 22:10

Matthew Erwin · Answer 2 · 2015-04-08T04:37:23.037

For T-SQL the following logic will perform the task that the Oracle code above does. This is generic general solution and will support Base-X to Base-10:

select
    sum(power(base,pos-1) *
            case when substring(cnv,pos,1) between '0' and '9' then 
                cast(substring(cnv,pos,1) as int) 
            else 10 + ascii(upper(substring(cnv,pos,1))) - ascii('A') end)
    from (values(reverse('01Z'), 36)) as t(cnv,base)
        left join (values(1),(2),(3),(4),(5),(6)) as x(pos)
            on pos <= len(cnv)

To use with other bases just use:

from (select cnv = reverse('FF'), base=16) as t

or

from (select cnv = reverse('101'), base=2) as t

Note that to support strings longer than 6 you would need to add more values to the position vector.

Base 36 to Base 10 conversion using SQL only

2 Answers2

Linked