CODEWITHSUNDEEP
sqloracle-databasereplaceoracle12cregexp-replace
hemalp108
hemalp108

Reputation: 1249

Oracle regular expression to replace numbers with alphabets

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;

Upvotes: 2

Views: 3023

Answers (2)

tranesend
tranesend

Reputation: 47

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;

Upvotes: 0

Dr Y Wit
Dr Y Wit

Reputation: 2020

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

Upvotes: 1

Related Questions