1

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?

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.