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 <= 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! :)
