Oracle regexp_replace numbers for letters

Question

I think this should be simple but I can't seem to figure out a way to do it other than nesting regexp_replace. I want to replace each number with a corresponding letter something like:

regexp_replace(regexp_replace(regexp_replace('147','1','A'),'4','D'),'7','G')

result:

ADG

but with a list operator like this

regexp_replace('12345','[1234567890]','[ABCDEFGHIJ]')

but of course instead of ADG I get

[ABCDEFGHIJ][ABCDEFGHIJ][ABCDEFGHIJ][ABCDEFGHIJ][ABCDEFGHIJ]

How do we know that 147 represents the first, fourth and seventh letter and not - for example - fourteenth and seventh? — Littlefoot
– Littlefoot, Commented Oct 10, 2019 at 17:33

user5683823 · Accepted Answer · 2019-10-10 17:34:00Z

2

You don't need regular expressions for this; you need the TRANSLATE function:

select translate('147', '1234567890', 'ABCDEFGHIJ') as translated from dual;

TRANSLATED
-------------
ADG

answered Oct 10, 2019 at 17:34

user5683823

Sign up to request clarification or add additional context in comments.

Comments

Nick Reed · Accepted Answer · 2019-10-10 17:37:44Z

1

This can't be easily done with REGEXP_REPLACE.

Regex doesn't really have the power to decide what to replace based on what it matches; it's usually only meant to make a match and leave the rest up to whatever programming language you're using. You might be able to swing this in something like Python, but as has been pointed out in another answer, for Oracle, REGEXP_REPLACE is the wrong tool for the job.

answered Oct 10, 2019 at 17:37

Nick Reed

5,1094 gold badges19 silver badges39 bronze badges

Collectives™ on Stack Overflow

Oracle regexp_replace numbers for letters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related