Oracle11g - Convert alpha characters to numeric in select I am only able to convert alpha characters to numeric by nesting REPLACE. Is there a better way to handle this?

Desired conversion:

a - 1
A - 1
b - 2
B - 2
c - 3
C - 3
...
z - 26
Z - 26


Example:

select  '1a2b3c4d5e' as "before",
        REPLACE(REPLACE('1a2b3c4d5e','a','1'),'b','2') as "after"
  from dual


Output:

before      after
1a2b3c4d5e  11223c4d5e


I'm hoping there is an alternative, as none seemed to exist here. Maybe there is a clever way to use REGEXP_REPLACE?
Well, it won't win any code golf awards .. O.o ... but this should work .. the trick is you trying to convert higher letters into double digits . (ie z ==> 26)  if it was 1 to 1 .. TRANSLATE would work quite nicely .. however, I'm not sure to do it more elegantly .. O.o  (not sure if it's better than the REPLACE option or not .. lol)  I suppose with this logic as a base, you could easily turn it into a table driven approach .. using the w_sub query as the join to your mapping tables) ;)

  col after format a50

  with w_input as ( select '1a2b3c4zd5e' c from dual ),
     w_asc as ( select c, level i, ascii(substr(upper(c),level,1))  aa,
                    substr(upper(c),level,1)  cc
                 from w_input  connect by level &lt;= length(c)
               ),
     w_sub as (
        select c, i, aa, cc,
                 case when aa between 65 and 90  -- A-Z
                       then aa-64
                    when aa between 48 and 57  -- 0-9
                       then to_number(cc)
                    else 0     -- non number, non alpha ... could be punctuation, etc.
                 end  zz
          from w_asc
        )
  select c  before, 
        listagg(zz,'') within group (order by i)   after
    from w_sub
  group by c
  /

  BEFORE      AFTER
  ----------- --------------------------------------------------
  1a2b3c4zd5e 112233426455


Explanation:

1) w_input is just your input .. put whatever tests you want here.

2) w_asc: is taking the string, and turning each digit into a row ... 
   (it also yanks the digit, and converts it to ASCII code )

3) w_sub: this is your conversion/mapping logic - numbers 0-9 ==> 0-9 .. letters: a==>1  z==>26 ... case insenstive.

4) the final query uses listagg to bring the rows back together using the original column "i" to remember the order.

Cheers! :)