Oracle regular expression to replace numbers with alphabets

Question

I have a string which contains only numbers. I need to replace all digits in the string with a corresponding alphabet as below,

0   ->  A
1   ->  B
2   ->  C
..
9   ->  J

I tried with below using translate and replace functions and it works fine for me,

Forward :
    WITH T (ID) AS (SELECT '10005614827' FROM DUAL)
    SELECT ID, TRANSLATE(ID,'0123456789','ABCDEFGHIJ')  "TRANSLATE",
           REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ID,'0','A'),'1','B'),'2','C'),'3','D'),'4','E'),'5','F'),'6','G'),'7','H'),'8','I'),'9','J') "REPLACE"
    FROM T;

    Output:
        ID          TRANSLATE       REPLACE
        10005614827 BAAAFGBEICH     BAAAFGBEICH

Reverse:
    WITH T (ID) AS (SELECT 'BAAAFGBEICH' FROM DUAL)
    SELECT ID, TRANSLATE(ID,'ABCDEFGHIJ','0123456789')  "TRANSLATE",
           REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ID,'A','0'),'B','1'),'C','2'),'D','3'),'E','4'),'F','5'),'G','6'),'H','7'),'I','8'),'J','9') "REPLACE"
    FROM T;

    Output:
        ID          TRANSLATE       REPLACE
        BAAAFGBEICH 10005614827     10005614827

Is there any way to use regular expression to implement this?

WITH T (ID) AS (SELECT '10005614827' FROM DUAL)
SELECT ID, REGEXP_REPLACE(ID,'[0-9]','[A-J]')
FROM T;

Dr Y Wit · Accepted Answer · 2019-08-29 11:49:33Z

It's not possible in Oracle because of the limitations of current implementation.

More specifically, you cannot apply a function to matched value, you only can use backreferences in a form \n where n is a digit from 1 to 9.

For example, you can match each digit and repeat it as many times as it equals to.

column example format a40
with t(id) as (select '10005614827' from dual)
select id,
regexp_replace(id,'(1)|(2)|(3)|(4)|(5)|(6)|(7)|(8)|(9)|(0)','\1\2\2\3\3\3\4\4\4\4\5\5\5\5\5\6\6\6\6\6\6\7\7\7\7\7\7\7\8\8\8\8\8\8\8\8\9\9\9\9\9\9\9\9\9') example          
from t
/


ID          EXAMPLE                                 
----------- ----------------------------------------
10005614827 1555556666661444488888888227777777      
1 row selected.

But you can't apply any function to \n in replacing string.

On the other hand, in languages like Perl, Java, Scala... or even in PowerShell and others it's doable.

Example from Scala REPL.

scala>   val str = "10005614827"
str: String = 10005614827

scala>   // matching and converting each digit separately

scala>   "\\d".r.replaceAllIn(str, x => (x.group(0)(0)+17).toChar + "")
res0: String = BAAAFGBEICH

scala>   // marching and converting sequences of digits

scala>   "\\d+".r.replaceAllIn(str, x => x.group(0).map(x=>(x+17).toChar))
res1: String = BAAAFGBEICH

To complete the picture, model solution just for fun.

SQL> with t(id) as (select '10005614827' from dual)
  2  select *
  3  from t
  4  model partition by (id) dimension by (0 i) measures (id result)
  5  rules iterate(10)
  6  (result[0] = replace(result[0],iteration_number,chr(ascii(iteration_number)+17)))
  7  /

ID                   I RESULT
----------- ---------- -----------
10005614827          0 BAAAFGBEICH

translate is the very best approach in this case though. This is exactly what this function was made for.

PS. Scala equivalent for example above with a function applied to matched value instead of using backreferences.

"\\d".r.replaceAllIn(str, x => (x.group(0)*(x.group(0)(0)-48)))

tranesend · Accepted Answer · 2019-08-29 13:44:45Z

0

Using translate function I do not see problems. Having for example the string number '3389432543' you can convert it using

         SELECT TRANSLATE('3389432543','0123456789','ABCDEFGHIJ')
         FROM DUAL;

answered Aug 29, 2019 at 13:44

tranesend

476 bronze badges

1 Comment

Dr Y Wit Over a year ago

Did you manage to read until at least third sentence in original post?

Collectives™ on Stack Overflow

Oracle regular expression to replace numbers with alphabets

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related